Lock Problem When Inserting And Deleting Records
Jul 12, 2007
Hi all.
I have an application that is using a SQL compact edition database to save/process information.
I run a test that is creating two threads:
1. one is inserting data in Table1
2. the other one is deleting records from Table1
When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.
After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.
Any thoughts?
Thanks.
View 5 Replies
ADVERTISEMENT
Jul 19, 2006
Hello.
I need to insert some records to an accounting table and calculate the balance after that. Thus, other users can be trying to do the same. How to lock the db and make the other users wait until the right moment? I'm using SqlDataSource to do that.
Thanks.
View 7 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Jul 10, 2014
I have question on lock on table in SQL Server while inserting data using multiple processes at a single time into same table.Here are my questions on this,
1) Is it default behavior of SQL server to lock table while doing insert?
2) if yes to Q1, then how we can implicitly mention while inserting data.
3) If I have 4 tables and one table is having foreign keys from rest of the 3 tables, on this scenario do I need to use the table lock explicitly or without that I can insert records into those tables?
View 1 Replies
View Related
May 2, 2007
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece.
thanks.
View 2 Replies
View Related
Oct 31, 2007
Hello -- I have a Huge Table Tab1 (160 Mill Rows). I have a script that Inserts and Deletes from this Tab1 (This Script runs for a good 4/5 Hrs).
When the script is being executed, No other session can even do a "SELECT TOP 10 * FROM Tab1"
How do i avoid this? Any "NOLOCK" Keywords i should be specifying in my Script?
Thanks in advance
View 3 Replies
View Related
Aug 17, 2006
I have created a single Data Flow Task that reads a set of records from a source table and then makes determinations whether to insert, update or delete from a destination table. The data is basically being copied from one database to another with a small amount of data manipulation and lookups. The problem seems to be that when running the task, even a small amount of records read from the source table seem to take a long time for the task to finish. I feed the records into a Script Component (the brains) that sorts the records to three separate outputs. I use OLE DB Commands to perform the DELETE and UPDATE and an OLE DB Destination for INSERT. I thought that by using three separate database connections would help, but it just appears to be locked while trying to perform these commands against the same table.
Is there a way to control or route these three record sets in such a way as to perform them sequentially?
I know it's a bit of a simple question for some of you, but I'm just learning SSIS (but I like it!).
Thanks!
Jeff Tolman
E&M Electric
View 3 Replies
View Related
Nov 3, 2015
I am getting a number of deadlocks when inserting and deleting items from the same table.
The delete statement has a U lock and awaiting an IX lock on an index that covers the column in the where clause.
The insert statement has a IX lock and awaiting a U lock on the same index.
The delete statement is deleting about 5000 rows, where as the insert statement is inserting a single row.
Both these statements are found in stored procedures being called from LINQ to SQL.
I am wondering if there is a way I can prevent the delete statement taking the U lock out?My thinking being if the delete didn't take out the U lock then it would not deadlock with the insert. Are there any hints I could use to avoid the particular lock above?
I have seen various examples of multiple updates causing a deadlock, which can be fixed by adding multiple indexes. However, as I am inserting and deleting rows I imagine that all the indexes will need to be updated by both operations.
I have inherited the architecture and don't have the time to redesign everything at present. My backup plan is to deprioritize the delete and build in a retry mechanism.
However, it would be really good if I could find a more elegant way to handle deleting and inserting rows at the same time.
Deadlock trace information below...
11/02/2015 22:21:26,spid21s,Unknown,waiter id=process1cc9c68558 mode=IX requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process203f31b498 mode=U
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=721673 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1663f5d900 mode=U
[Code] ....
View 1 Replies
View Related
Jul 12, 2004
I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?
View 10 Replies
View Related
Feb 25, 2005
I have a table with a load of orphaned records (I know... poor design)
I'm trying to get rid of them, but I'm having a brain cramp.
I need to delete all the records from the table "Floor_Stock" that
would be returned by this select statement:
SELECT FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT =
FLOOR_STOCK.PRODUCT LEFT OUTER JOIN
BOD_HEADER ON FLOOR_STOCK.PRODUCT =
BOD_HEADER.PRODUCT AND FLOOR_STOCK.SITE =
BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))
I was thinking along the lines of:
DELETE FROM FLOOR_STOCK INNER JOIN
(SELECT FLOOR_STOCK. PRODUCT, FLOOR_STOCK.SITE
FROM PRODUCT_MASTER INNER JOIN
FLOOR_STOCK ON PRODUCT_MASTER. PRODUCT =
FLOOR_STOCK.PRODUCT
LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK. PRODUCT =
BOD_HEADER. PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
(PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))) F ON
FLOOR_STOCK. PRODUCT = F. PRODUCT
AND FLOOR_STOCK.SITE = F.SITE
... but Sql Server just laughs at me: "Incorrect Syntax near the keyword INNER"
View 14 Replies
View Related
Mar 3, 2004
How do I delete one record from one table and cascade down all related tables?
Mike B
View 14 Replies
View Related
Aug 14, 2006
Hey all,
Here is the scenario. I'm working with two tables:
Contact1
Conthist
Contact1 contains basic contact information and conthist contains history records for those contacts. Conthist can hold many records related to a single contact1 record.
The link between the two tables is a column called accountno.
I'm trying to delete any records in conthist that have an accountno that does not exist in contact1. The queries that I've tried keep returning conthist records that do actually have a matching accountno.
Any help would be appreciated.
Thanks,
Tony
View 4 Replies
View Related
Apr 12, 2007
Whenever I try to delete records from a table joined to another table, like so:
DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3)
I get the error:
Major Error 0x80040E14, Minor Error 25501
> DELETE [tblTransaction Detail Local] FROM [tblTransaction Detail Local], [tblTransaction Header] WHERE ([tblTransaction Detail Local].[Transaction ID] = [tblTransaction Header].[Transaction ID]) AND ([tblTransaction Header].[Computer ID] = 3)
There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = FROM ]
This is an SQL CE database, and SQL Server Management Studio. Any ideas?
View 1 Replies
View Related
May 18, 2006
I have a couple SQL tables that have been appended to daily over the last two years. There is now about 50,000,000 records in the table. Does anyone know the fastest way to delete records before a certain date to shorten these tables? Delete queries and everything else I've tried is taking way too long.
View 13 Replies
View Related
May 21, 2004
Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59.
So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed.
DTS the ones to keep to another table, drop the old and rename the new table?
Bulk copy out, truncate and bring back in?
DTS to text, truncate and import back?
Other ways?
Never worked with such a large table and need a little experienced guidance.
Thanks for the help
View 1 Replies
View Related
Jan 5, 2002
My Web Host does not provide administrative privilages to the SQL server I have access to. I would like to delete tens of thousands of records from two of my tables without writing to the Transaction Log. Is what I'm trying to do is delete these records quickly without utilizing any of the alotted space my web host has set aside for my transaction log (they give me 50 mb and I go way over that when I run a DELETE statement)
What is the best way to do this?
View 3 Replies
View Related
Jul 8, 1999
I need a sql statement to delete duplicate records.
I have a college table with all colleges in the nation.
I noticed that all of the colleges were listed twice.
How do I delete all of the duplicate records.
Here is my table.
Colleges
-------------------
schoolID - smallint NOT NULL,
schoolName - varchar(60) NULL
Can someone help me out with the sql statement???
I'm running SQL Server 6.5.
- ted
View 3 Replies
View Related
Aug 27, 2004
Hi All,
I am having one table named MyTable and this table contains only one column MyCol. Now i m having 10 records in it and all the records are duplicate ie value is 7 for all 10 records.
It is something like this,
MyCol
7
7
7
7
7
7
7
7
7
7
Now i m trying to delete 10th record or any record then it gives me error
"Key column information is insufficient or incorrect. Too many rows were affected by update."
What should i do if i want only 4 records insted 10 records in my table?
How do i delete the 6 records from table?
Plz help me.
Regards,
Shailesh
View 3 Replies
View Related
Jun 3, 2008
I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random)
For example.
CREATE TABLE [Employee] (Id int, Name varchar(50))
CREATE TABLE [Company] (Id int, Name varchar(50))
CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int)
CREATE VIEW [dvEmployee]
AS
SELECT *
FROM [Employee] INNER JOIN [EmployeeCompany]
ON [Employee].[Id] = [EmployeeCompany].[EmployeeId]
CREATE TRIGGER [dvEmployeeUpdate]
ON [dbo].[dvEmployee]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE EmployeeCompany
SET Status = INSERTED.Status
FROM EmployeeCompany, INSERTED
WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId
AND EmployeeCompany.EmployeeId = INSERTED.EmployeeId
END
Because the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table?
Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables.
I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into?
View 2 Replies
View Related
Jan 15, 2006
help me out on this one.
i have 2 text boxes in my page.
user enter any number in those two text boxes.
i slect that many record randomly from my main table, and put it into two another tables.
now the problem is coming in how to delete those records which were randomly selected from main table in main table.
for eg
main table contains
srNo. UswerID
1 abcd
2 trtr
3 tret
4 yghg
5 jjhj
user enters in text box1 '2' and in text box2 `1'
so total of 3 random records are selected
and put it into two another table say
table1
sr.no UserID
2 trtr
and table2 contains
sr.no. userid
3 tret
5 jjhj
now i want to delete these records which are sr.no 2,3,5 from the main table.
how do i do it as user can enter any number in the text box.so writing multiple delete statements would not be possible.
how do i write statements or help me with logic.
View 13 Replies
View Related
Dec 1, 2006
gaurav writes "respected sir
here i have a question
how we can delete duplicate records through query in SQL Server
thanks"
View 2 Replies
View Related
Oct 17, 2007
Hi
I wanted to do a delete rows from a group of table. These tables have a common column UserID. I heard that there is something called ondelete cascade. But I don't know how to set it up and utilise it. Could someone tell me how to do it. Or point me to a tutorial which shows how to do it.
Thanks
View 4 Replies
View Related
Jul 20, 2005
I have a database that is used to store a lot of data. We load the data on adaily basis, several thousand records per day. The Log file is not needed,so whats the best way to delete the records in it and reduce the sizeThanksDerrick
View 1 Replies
View Related
Dec 2, 2007
Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks
View 5 Replies
View Related
May 29, 2008
Hello all,
I have a DTS package set up to import a text file on a daily basis. I need to dump the data in 2 table after 7 days of the last import .this is the code that I have
Delete From TblTemp
date(Day(-7), CurrentStamp).
But for some reason it deleting the data right after it imports it. And it doesn't delete anything out of the other table.
Thanks in advance
View 2 Replies
View Related
May 8, 2005
I have a function that opens a connection to an SQL database, issues a
SELECT command, and reads the records with an OleDbDataReader. As the
records are read, any that match certain criteria are deleted with a
DELETE command. Simplified example code is shown below:
Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed to
SQL Server, I get a database timeout error when attempting to do the
DELETE. I suspect the reason is that the connection the reader has open
has the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property
"Mode=Share Deny None". I wonder if there is some similar way to tell
SQL Server to allow editing of records that are open for reading with
an OleDbDataReader.
Any help would be appreciated.
View 3 Replies
View Related
May 26, 2015
I have this table:
CREATE TABLE [dbo].[ACT_SECUNDARIA](
[CODACTIVIDADE] [int] IDENTITY(1,1) NOT NULL,
[CODCTB] [int] NOT NULL,
[CODCAE] [int] NULL,
[CODSECTOR] [int] NOT NULL,
[Code] ....
I want to delete every record that has more than one entry (codctb; codcae)
For example: if there are three records with the same codctb and codcae I want to delete two so that there can only be one.
How can I achieve this using t-sql?
View 2 Replies
View Related
Aug 10, 2015
I wrote a script to archive and delete records rom a table back in 2005 and 2009.
I can't seem to get the syntax right. Any sample script to simply archive and delete records?
This is what I have so far.
DECLARE @ArchiveDate Datetime
SET @ArchiveDate = (SELECT TOP 1 DATEPART(yyyy,Call_Date)
FROM tblCall
ORDER BY Call_Date)
--SELECT @ArchiveDate AS ArchiveDate
DECLARE @Active bit
[Code] ....
View 9 Replies
View Related
Aug 9, 2006
Rajarajan writes "Kindly don't ignore this as regular case.
This is peculiar.
I need to delete one of duplicate records only if they occurs consecutively.
eg.
1. 232
2. 232
3. 345
4. 567
5. 232
Here only the first record has to be delete. Kindly help me out.
Thank you.
Regards,
R.Rajarajan"
View 1 Replies
View Related
Jul 6, 2006
I loaded one table via SSIS and found that it contained many duplicate records (from the input source). I can create a SQL task to delete them, but I wonder if SSIS offers and task "out of the box" to delete dups?
TAI,
barkingdog
View 6 Replies
View Related
Nov 15, 2007
I've got a table with a unique column, "id". I've got the id values of about 300,000 records. These records need to be DELETEd from this table. Is there a way to do this in batch? I can't imagine the only way to do it is:
DELETE FROM Table WHERE id = 1 OR id = 2 OR id = 3... OR id = 300000
View 10 Replies
View Related
Jul 4, 2007
I use the following function (in the BLL) to delete some records:
Public Function DeleteStep4Dashboards() As Boolean
Try
adpDashboards.DeleteStep4Dashboards()Catch ex As Exception
Return False
End Try
Return True
End Function
How can I catch the sql database errors when deleting the records goes wrong.
View 5 Replies
View Related
Mar 27, 2008
I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase Description From_Date To_Date------------------------------- --------------- ----------------Desktop 2/2/2007 2/3/2007Mouse 2/1/2007 28/1/2007Laptop 5/1/2008 15/3/2008Speaker 4/1/2008 21/1/2008
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Please provide me with full stored procedure
Thanx in advance
View 4 Replies
View Related
