Got a bizarre issue here.
Basically I have two separate threads which are accessing the same table. One is updating a single row whilst the other is selecting a group of records. There is no overlap between these records.
The basic issue is that it’s occasionally locking up. I’m using SQL Server 2008 R2 and have tried it using both FireDAC and ADO with similar results.
I’ve been tracing what’s happing at the server itself (when using ADO) and what I’m seeing is very strange. The update happens first and appears to use an “sp_prepexec” statement to prepare and execute the update command. Immediately afterward it issues a “sp_unprepare” statement to unprepare the previous command.
The issue is that my server logic spawns a separate thread in response to this update which then causes the select to take place in a separate thread. The select uses an “sp_executesql” to retrieve the data.
Now if these two processes happen completely sequentially all is well but, now and again, it seems that the second thread manages to issue its sp_executesql command to the database AFTER the first thread’s sp_prepexec but BEFORE the sp_unprepare, i.e. it manages to get “in between” the two statements from the first thread.
When this happens everything locks up. The second thread’s sp_executesql blocks completely - SQL Server’s Activity Monitor shows it as a “SUSPENDED SELECT” with a lock type of LCK_M_S. Now I can understand this as the first thread is in the middle of an operation (transaction?) so the second thread has to wait.
What’s bizarre is the first thread blocks too. What I’d expect to see is the first thread complete its transaction with the sp_unprepare statement, at which point the second thread would unblock. This doesn’t happen.
What happens instead is everything stops - there is no sign of the first thread’s sp_unprepare. The second thread’s blocked sp_executesql statement sits there until it times out after 30 seconds, at which point the first thread’s sp_unprepare appears.
Do you have any ideas what’s going on here? At first I thought it was a database issue but I can’t see how this can be the case. I’m now suspecting that there’s something going wrong inside the RO framework which is blocking the first thread from even issuing the sp_unprepare statement and completing its transaction until the second thread’s sp_executesql has completed, which obviously won’t happen. The result is both threads lock up until the second thread’s query times out.
This is driving me totally insane so any ideas would be helpful.
