I have encountered a deadlock issue in <A href="https://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?
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!