Excuse the odd topic. I often like to experiment with SQL Server and create odd situations which we normally should be avoiding. One of those things is creating SQL Statement which get blocked and creating a timeout. One of the simplest ways to run a query against a table which is locked in another in another session. To do this do the following:
1) Create a lock on a table.
The example below demonstrates a lock on the Person table in the AdventureWorks2012 sample table. The key points which make this work is to create a transaction, and then issuing a query using the TABLOCKX and HOLDLOCK table lock hints. This will force an exclusive lock on the table. To keep the lock for significant amount of time we specify that the transaction stays alive for a minute by introducing the WAITFOR DELAY ’00:01:00′ command, which will prevent any further execution of statements until a minute has passed.
|
1 2 3 4 5 6 7 |
USE AdventureWorks2012; GO BEGIN TRANSACTION SELECT * FROM Person.Person WITH (TABLOCKX, HOLDLOCK); WAITFOR DELAY '00:01:00' ---Wait a minute! ROLLBACK TRANSACTION --Release the lock |
2) Now open up another session in SQL Server (new query from Microsoft SQL Server Management Studio), and execute the following query:
|
1 2 3 |
USE AdventureWorks2012; GO SELECT * FROM Person.Person; |
You will notice that after this query is excuted, the query seems to run forever. It will actually never execute until the query in step 1 is finished.
3) Check for blocked transaction. You can find this by opening up an new query in Microsoft SQL Server Management Studio and executing the following query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [master] GO SELECT session_id ,db_name(database_id) as 'Database_Name' ,sql_text.text as 'SQL_Blocked' ,blocking_session_id ,wait_time ,wait_type ,last_wait_type ,wait_resource ,transaction_isolation_level ,lock_timeout FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) as sql_text WHERE blocking_session_id != 0 |
I will explain in more detail how this blocking query works in a later post.
If you have any other tips on how to create a blocked transaction please share them in the comments below.
About Carlos Ferreira
Thanks, Carlos. I needed this info in a hurry.
Your Welcome! Please drop by again!
Carlos
Thank you. It helped me test some scenarios
Your Welcomed!
i issued begin transaction and then select * from table with (tablockx,holdlock); wait for delay ’00:02:00′ and was able to still run queries against the same able from a different new query window before the 2 minute ended as well
Hi – you need the Begin transaction and rollback transaction (or commit transaction) as well for this to work – otherwise the second query will execute right away. Try ‘begin transaction; select * from table with (tablockx,holdlock); wait for delay ’00:02:00′; rollback transaction;’