April 19, 2011 at 12:43 pm
When a transaction is in progress and it locks a table for either an insert or an update, can someone explain to me how another process "waits" for it to finish before trying to insert or update into the same table or row? Is there some sort of queue involved internally in SQL Server? How does the timeout factor play a role? and how many times does a waiting process try before giving up. How does SQL Server do this?
April 19, 2011 at 12:47 pm
Ray Cacciatore (4/19/2011)
When a transaction is in progress and it locks a table for either an insert or an update, can someone explain to me how another process "waits" for it to finish before trying to insert or update into the same table or row? Is there some sort of queue involved internally in SQL Server? How does the timeout factor play a role? and how many times does a waiting process try before giving up. How does SQL Server do this?
SQL Server places locks on rows or tables. If there is a lock, the process will sit and wait for the lock to be removed. This is internal to SQL Server. Server side, there are no timeouts or retries. The process will sit blocked indefinitely waiting for the other process to release the lock. Timeouts and retries are strictly client-side functions, where the client app gives up waiting, or else disconnects and reconnects and retries the same query. But this won't speed up the process. If you are getting timeouts or retries, you are looking at app situations caused by a long-running query, running long because of blocking. Server side, SQL is indifferent to how long something takes.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply