How to push the Database into deadlock state?

455 pts.
Database deadlock
Database Failover
Database security
SQL Database
SQL Query
SQL Server
Hi, We are doing Database FailOver testing (DBFO). As part of this, we want to check if primary db is in deadlock state then what happens? whether the transactions will go to back up db or not? How to get the primary db into deadlock state? Appreciate your help.

Answer Wiki

Thanks. We'll let you know when a new response is added.

What database platform are you using? You’ve listed three different ones in your tags.

In general if the database server was in the middle of a deadlock and then failed the parts of the transactions which had been written to the log would be rolled back when the backup system came online. As neither transaction has been committed, neither of the transactions changes would be committed when the backup server came online.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kccrosser
    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...
    3,830 pointsBadges:
  • Saimadhu
    Thanks Kccrosser for detailed info.
    455 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: