Creating blocks on purpose for testing

Question

Post reply

Creating blocks on purpose for testing

webrunner

SSC-Dedicated

Points: 31866
More actions
August 31, 2009 at 3:37 pm

#214472

Hello,
Could someone suggest a strategy, or some sample code, for creating blocking on purpose - for example in the AdventureWorks database, in order to test out a block monitoring script?
I've tried one based on updating the Person.Address table in an uncommitted transaction, but it still seems to complete and by the time I try to run a SELECT against the table, there is no blocking - the SELECT succeeds.
Also, if there are different types of blocking scenarios I should be looking for based on they types of locks that can lead to trouble, I would be grateful for help with which ones I should check. At the moment, I am using the query below, which I got from a training class.
I want something where the blocking can last for a minute or two so I can see it show up in the block monitor script results. But at the same time not completely drag the SQL server to its knees. It's only a test server, but still.
Thanks for any help,
webrunner
------
[font="Courier New"]SELECT t1.resource_type AS [lock_type],
db_name(resource_database_id) AS [database],
t1.resource_associated_entity_id AS [blk_object],
t1.request_mode AS [lock_req],
t1.request_session_id AS [waiter_spid],
t2.wait_duration_ms AS [wait_time],
(SELECT text FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT substring(qt.text, r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE r.statement_end_offset END - r.statement_start_offset / 2)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],
t2.blocking_session_id AS [blocker_spid],
(SELECT text FROM sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt]
FROM sys.dm_tran_locks AS t1, sys.dm_os_waiting_tasks AS t2
WHERE t1.lock_owner_address = t2.resource_address[/font]
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

If you do an explicit

BEGIN TRAN

then select, update or delete something, the locks obtained by the DML statement should be maintained until an explicit commit or rollback is issued on the same connection or the connection is terminated. If you do this in an active query window, you can execute the begin tran and the DML and then wait to issue the commit or rollback until you've finished testing whatever you'd like.

And then again, I might be wrong ...
David Webb

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

If you want to simulate more real time work, add a waitfor() inside the transaction for a random delay and then commit the transaction. Wrap that in a loop, perhaps with a waitfor in the loop, but outside the transaction to show things over time.

while loop start

waitfor (random sec)

begin tran

start something (insert, select, etc.)

waitfor (random)

commit

end

webrunner SSC-Dedicated Points: 31866 More actions · Answer 3

David Webb (8/31/2009)
If you do an explicit
BEGIN TRAN
then select, update or delete something, the locks obtained by the DML statement should be maintained until an explicit commit or rollback is issued on the same connection or the connection is terminated. If you do this in an active query window, you can execute the begin tran and the DML and then wait to issue the commit or rollback until you've finished testing whatever you'd like.

Thanks - your answer made me think of another possibility. I think I had both the open transaction and the select statement in the same query window. Perhaps that's why I wasn't getting the expected delay? If both statements are in the same SPID, then there is no other SPID to be blocked - maybe even the select statement is actually part of the transaction itself? I will try them in separate query windows to see if that works.

Thanks again for your help!

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31866 More actions · Answer 4

Steve Jones - Editor (8/31/2009)
If you want to simulate more real time work, add a waitfor() inside the transaction for a random delay and then commit the transaction. Wrap that in a loop, perhaps with a waitfor in the loop, but outside the transaction to show things over time.
while loop start
waitfor (random sec)
begin tran
start something (insert, select, etc.)
waitfor (random)
commit
end

Thanks, Steve - I didn't know about the waitfor() statement. I will experiment with it as well.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html