Wait for a while

Published on: 22nd Dec 2016

Updated on: 19th May 2021

Overview

During the test of your application, you may want to simulate the concurrent user access by locking some resources and see if you application will crash or not. This can be done easily.

How to simulate the concurrent user access

To put your process into sleep mode, you have to execute waitfor delay. First, get a random value to be used as waiting time. Then, pass it to waitfor delay.

declare @wait_time nvarchar(255)

print 'start working on it.. => current ms: ' + cast(datepart(millisecond, getdate()) as nvarchar)

set @wait_time = '00:00:00.' + left(cast(cast(rand() * 1000 as int) as nvarchar), 3)
waitfor delay @wait_time

print 'im done and waited for ' + @wait_time
    + ' => current ms: '
    + cast(datepart(millisecond, getdate()) as nvarchar)

For example, you want to get the sales record with the following query.

select *
from tb_sales 

To simulate blocking to the above query, you have to run the following query before the above. It will lock tb_sales table for 5 seconds and then release it.

begin tran

select *
from tb_sales 
with (tablockx)

waitfor delay '00:00:05'
rollback

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.