I am doing a test for deadlocking tables on MSSQL, oracle, sybase, db2, teradata and informix databases and for this I need to deadlock a table for select and therefore on issueing a simple select statement I want the database to return the deadlock error
I was able to find out that this can be done on db2 with command "db2 +c update" and then doing a select on the table from another prompt.
Could you please let me know how I can replicate the same sceario on MSSQL, oracle, sybase, teradata and informix databases
Mathew - please could you explain your goal here? You want the engine to return a deadlock error on issuing specifically a select statement - why? What do you want to use that information for?
Just so I understand - you do not expect your select statement to participate in the deadlock, correct? You want your select statement to be
issued after the deadlock occurs, and to be blocked by it, returning an error?
Mathew07, I don't want to get do picky about this but it appears you are using the wrong terminology (as shammat pointed out).
A Deadlock happens with 2 transactions and 2 tables and involves a change (Insert, Update or Delete). As an example:
Thread 1 has a Share lock on Table 1 and wants an Exclusive lock on Table 2.
Thread 2 has a Share lock on Table 2 and want a Share lock on Table 1.
These transactions are deadlocked and can not continue because Thread 1 is waiting for Thread 2 to release its lock while Thread 2 is waiting for Thread 1 to release its lock. Eventually one of them will time out and get out of the others way.
What you want is a simple Lock timeout. As an example:
Thread 1 has an Exclusive lock on Table 1 (this involves an Insert, Update or Delete).
Thread 2 wants a Share lock on Table 1. It can't get it because Thread 1 already has an Exclusive lock.
The Reason this terminology is important is the answer you want depends on the situation. Setting up a Deadlock (something like what hillcat did) is completely different from setting up a simple Lock Timeout (something like what PMASchmed did).
select * into pd1 from master..spt_values where type='P'
create index ix1 on pd1 (number)
update pd1 set type='P' where number=0
select * from pd1 (holdlock) where number=2047
update pd1 set type='P' where number=2047
Session 1 wait for lock to be released
select * from pd1 (holdlock) where number=0
Session 2 fail with
Msg 1205, Level 13, State 18, Line 1
Transaction (Process ID 367) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.