StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>based on the comments in <a href="https://stackoverflow.com/questions/4609217/sql-server-the-misleading-xlock-optimizations/4609384#4609384">@Martin's answer</a>, here is a little script (run the different parts in different SSMS windows to test the lock preventing a SELECT:</p> <pre><code>-- --how to lock/block a SELECT as well as UPDATE/DELETE on a particular row -- --drop table MyTable --set up table to test with CREATE TABLE MyTable (RowID int primary key clustered ,RowValue int unique nonclustered not null) --populate test data ;WITH InsertData AS ( SELECT 4321 AS Number UNION ALL SELECT Number+1 FROM InsertData WHERE Number<9322 ) INSERT MyTable (RowID,RowValue) SELECT Number, 98765-Number FROM InsertData ORDER BY Number OPTION (MAXRECURSION 5001) ----------------------------------------------------------------------------- -- #1 --OPEN A NEW SSMS window and run this -- --create lock to block select/insert/update/delete DECLARE @ID int BEGIN TRANSACTION SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE RowID=6822 PRINT @ID --COMMIT --<<<only run the commit when you want to release the lock --<<<adfter opening the other new windows and running the SQL in them ----------------------------------------------------------------------------- -- #2 --OPEN A NEW SSMS window and run this -- --shows how a select must wait for the lock to be released --I couldn't get SSMS to output any text while in the trnasaction, even though --it was completing those commands (possibly buffering them?) so look at the --time to see that the statements were executing, and the SELECT...WHERE RowID=6822 --was what was where this script is blocked and waiting SELECT GETDATE() AS [start of run] SELECT '1 of 2, will select row',* FROM MyTable Where RowID=6822 go DECLARE @SumValue int SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT GETDATE() AS [before transaction, shouldn't be nuch difference] BEGIN TRANSACTION SELECT @SumValue=SUM(RowID) FROM MyTable WHERE ROWID<6000 SELECT GETDATE() AS [in transaction, shouldn't be much difference] , @SumValue AS SumValue --everything to here will run immediately, but the select below will wait for the -- lock to be removed SELECT '2 of 2, will wait for lock',* FROM MyTable Where RowID=6822 SELECT GETDATE() AS [in transaction after lock was removed, should show a difference] COMMIT ----------------------------------------------------------------------------- -- #3 --OPEN A NEW SSMS window and run this -- --show how an update must wait UPDATE MyTable SET RowValue=1111 WHERE RowID=5000 --will run immediately GO UPDATE MyTable SET RowValue=1111 WHERE RowID=6822 --waits for the lock to be removed ----------------------------------------------------------------------------- -- #4 --OPEN A NEW SSMS window and run this -- --show how a delete must wait DELETE MyTable WHERE RowID=5000 --will run immediately go DELETE MyTable WHERE RowID=6822 --waits for the lock to be removed </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload