Any database can be placed into a deadlock (at least temporarily) by the following:

Thread A:
select … from tableA for update… (succeeds)

Thread B:
select … from tableB for update… (succeeds)
select … from tableA for update… (blocks on Thread A)

Thread A:
select … from tableB for update … (blocks on Thread B – deadlock)

However, most current databases are quite good at detecting these conditions, and will usually quickly terminate one of the threads. Typically, it will kill the thread which first “detected” the deadlock – A in this case.

You may be able to configure the database deadlock detection timeout to extend for a while, to allow you to fail the database while in this condition. Otherwise, you may need to generate a set of transactions with a series of tables, to try to defeat the deadlock detection algorithms. The obvious way is just to use a series of similar tables, A, B, C, D, … and threads, such that each thread uses the “next” pair of tables, and the “last” thread uses the last table and table A. For example, a 4 table version would be:
Thread A:
select… from A for update
select … from B for update
Thread B:
select … from B for update
select … from C for update
Thread C:
select … from C for update
select … from D for update
Thread D:
select … from D for update
select … from A for update

(Observant developers will immediately think about doing this with a single procedure that takes a starting table name [or pair of table names] and can be queued/scheduled to generate any number of threads.)

Note that you will need to sequence these, such that the second Select for Thread A doesn’t run until the first Select from Thread B succeeds, etc. – each of these Threads must succeed on the first table in order for this to create a nice cyclic lock.

Depending on your database, this can be done in a variety of ways. I would probably create another thread that simply updates yet another table with a “sequence” value, timed to occur at some interval (e.g., 1 second), and then have a simple loop in each of these that watched for the appropriate sequence value to procede, e.g.:

while vSeq <> [myvalue]
sleep [some amount];
select nextcode from seqtable into vSeq;
wend;
(obviously, use your appropriate DB syntax here)

Alternately, if you can control the starts of these processes, they can just wait for specific times to allow the next thread to get started and then proceed.

Starting the threads is also DB dependent, but generally trivial – in Oracle, I would probably just use the jobs package (making sure I had more concurrent job queues than the number of threads I needed!).

Note that you will probably need to extend the default time to wait on a table lock, as many databases will now kill a thread that waits on a table lock for too long (which may be just seconds).

All in all, a fun little exercise…

Thanks Kccrosser for detailed info.