Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Other SQL Server Topics (2005)  Prepared statement creates deadlock

Author  Topic  Gladwin Starting Member 1 Post Posted - 2009-06-02 : 12:46:46 Issue: Prepared statement creates deadlock when multiple threads try to update the diff rows of a table which is locked by other thread Description: When we use prepared statement to insert/update the records in a table with multiple threads, the prepared statement creates deadlock and rest threads are terminated without doing any operation and the thread which has created the deadlock releases and completes the operation. For example: Thread 1 and Thread 2 are identical threads performing same kind of operation. Both threads acquire a lock on a table and insert a row in a table but do not release the lock after performing the insertion. The Thread 1 now try to update the a row of a table with acquiring a lock and will not get the resources as the Thread 2 has not released the lock, in the same way Thread 2 also will try to update a row with acquiring a page level lock and will not get resources as the Thread 1 has not released the lock. This creates the deadlock and Thread 2 will be terminated and released the lock. Now Thread 1 can acquire the lock and finish the operation. Note: The connection object used by Thread 1 for insert and update remains the same, as with Thread 2 also. The same issue(deadlock) does not observe if the statements are used instead of prepared statements. e.g. Connection1 > BEGIN TRANSACTION Connection2 > BEGIN TRANSACTION Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCBA', ' ', ' ', ' ', ' ') Connection2 > INSERT INTO example1 VALUES (300, 'AAAB', 'CCCZ', ' ', ' ', ' ', ' ') Connection2 > UPDATE example1 SET column3 = 'CCBA' where column1 = 105 At this point, Connection2 may be blocked by Connection1 because Connection1 may be holding a lock where Connection2 needs to update. Connection1 > UPDATE example1 SET column3 = 'CCCZ' where column1 = 305 At this point, Connection1 may be blocked by Connection2, resulting in a deadlock.