Deadlock issue in SQL Server 2005
I have encountered a deadlock issue in <A href="http://searchsqlserver.techtarget.com/topics/0,295493,sid87_tax301323,00.html”>SQL Server 2005</a>.
According to the trace flag 1204 report, the following two queries are causing the deadlock:
1. Simple insert statement
INSERT INTO TABLE1 (FIELD1, FIELD2, FIELD3) VALUES (@INTVAL, @NTEXTVAL, @CHARVAL)
- Provide IX lock
2. Simple select statement with READPAST
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 WITH READPAST WHERE FIELD1 > @VALUE
- Provide S lock
The above queries handle online transactions. One service will only run the insert statement and the other service will only run the select statement. This deadlock error happens intermittently.
I'm wondering if this deadlock happens because FIELD2 is a ntext field? If I'm using NOLOCK in the SELECT statement, would it be possible that I may miss retrieving FIELD2 information since it is the ntext field that keeps data in a separate page?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 6, 2009 5:30 PM
UPDATED:
July 24, 2009 5:58 PM
Answer Wiki:
Using NOLOCK wouldn't cause you to miss data because it's a NTEXT field. It could cause you to get bad data back because of the dirty read. What's the transaction isolation level of your transactions. With the default locking that SQL server does you shouldn't get a deadlock when those two queries are run at the same time. Unless your running into a problem with lock escalation. If that's the problem you can try forcing the locks at the row level so that page level locks aren't being taken.
To see all answers submitted to the Answer Wiki: View Answer History.
The query above is Read Committed isolation. Due to the log report from trace log report, its indicate the insert query lock page 102971 (IX lock) and the select query lock page 102945 (S lock). Would it be cause by lock escalation (which will only escalate to lock to table level)?
And I will try the suggestion to use row level lock hope that it can solve it.
It sounds like the deadlock would be caused by lock escalation in this case.
You can also try turning on snapshot isolation mode for the database. This will increase the load on your tempdb database, but should resolved the issue.
If you don’t want to enable snapshot isolation mode you could try using the READPAST query hint. This will tell the query to simply skip any rows which are locked (the row you are inserting).