Deadlock issue in SQL Server 2005

Tags:
SQL Server 2005
SQL Server 2005 error messages
SQL Server errors
SQL Troubleshooting
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 1801
    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.
    10 pointsBadges:
    report
  • Denny Cherry
    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).
    66,005 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following