Deadlock occurs on one separate table, with 2 users doing a simple statement
Final Solution
In fact, we were doing row by row traversal, creating the worktable and session ID, and also storing the proc, which DELETE and INSERT from temp to the main table all at once. No more deadlocks!
Final process:
- REMOVE FROM HEAT RUNNING (with sessionID, just in case ...)
- INSERT INTO TheTable_work (line by line with ADODB)
- I am calling a stored procedure that does:
- START OF OPERATION
- REMOVE FROM TESTS (with some conditions)
- INSERT INTOTTABLE FROM TheTable_work (1 expression)
- COMMIT
- DELETE FROM THETable_work (clear)
We are thinking about it because of the index lock, but I am awaiting confirmation from the DBA (explained here ).
Isolation levels didn't change anything (we tried every feature, even toggling READ_COMMITTED_SNAPSHOT on and off)
I have an application that deadlocks my database when 2 users "write" to the database at the same time. They don't work with the same data as they have different IDs, but they work in the same table.
User 1 (id = 1), User 2 (id = 2)
Process
User 1 does : 1 DELETE statement, followed by 3000 INSERT
User 2 does : 1 DELETE statement, followed by 3000 INSERT
User 2 DELETE in the middle of user 1 INSERT (example : after 1500). Result in Deadlock.
Statements (examples, but they work)
DELETE FROM dbo.TheTable WHERE id = 1 /* Delete about 3000 lines */
INSERT INTO dbo.TheTable (id, field2, field3) VALUE (1 , 'value2','value3')
I am using ADODB (Microsoft Access ...), so I am doing a simple query like:
ConnectSQLServer.Execute "DELETE or INSERT statement"
Error message
Run-time error '-2147457259 (80004005)':
Transaction (Process ID76) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Additional Information
- No transaction, just a simple INSERT or DELETE query, one by one
- RecordSet is not involved and does not open
- We tried to find traces of the dead end, but the graphics did not appear, the DBA is trying to understand why
- I would like to understand why they lock each other up and not just "wait" until the other is finished! How can I improve my work? =)
- Access: This is the access interface with the SQL server. No tables related, vba code pushed queries through ADODB connection.
Edit: Deadlock Graph
On the left we have 1 (out of 3000) INSERT statements, on the right we have DELETE statements.
UPDATE
I deleted the index on the column that is being used in the DELETE statement and I can no longer reproduce the dead end! Not a clean solution, but a temporary one. We think of INSERT 3000 lines in temp table, and then copy from temp table to TheTable at once (DELETE and INSERT in stored procedure).
Thank,
Seb
source to share
DELETE
will block updates on pages:
Update (U)
Used for resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions read, block, and potentially update resources later.
And INSERT
will keep intentional / exclusive blocking on the same pages.
Exclusive (X)
Used for data modification operations such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be resource at the same time.
Intention
Used to establish a lock hierarchy. The types of intent locks are (IS), intent exclusive (IX) and split with intent exclusive (SIX).
If you want to run both requests at the same time, each will have to wait for the other at a specific time.
I suggest you run each insert in a separate transaction unless rollback is required for the entire set of inserts.
Or try to remove the parallelization or do these operations at different times.
In short:
1) Try to make sure each INSERT is correctly received before moving on to the next.
2) Or try to avoid doing both operations at the same time.
Another assumption is to adjust the transaction isolation level for the sessions that are causing the problem. Check out the following documentation.
source to share