How to test whether the Database tables designed to avoid deadlock occurence?

455 pts.
Database administration
Database deadlock
Database design
Database Failover
Database security
SQL Database
SQL Query
SQL Server
I heard from our dev team that our db tables are designed such that there is no chance of deadlock. How to test whether the Database tables design avoids deadlock? By reviewing the db scripts (table creation) can we find whether the db table design is deadlock proof or not? What else can we do? Thanks for help.

Answer Wiki

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

I am not aware of any database table design that will tell if the system will be “dead lock” proof. Avoiding “deadlock” is more a function of the particular database system – Oracle, DB2, SQL and the resources available (pool, cache, threads, memory), number of users, etc. Check with the particular DBMS (database management system) vendor.


There is no way to design a table to be deadlock proof. A deadlock occures when one transaction locks a resource, then another query tried to access the resource creating a block. When the first query tries to access the resource a second time a deadlock occurs and the engine will rollback one of the queries.

This is a very simple example of a deadlock. Deadlocks can occur over several tables as well.

Discuss This Question: 6  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.
  • carlosdl
    I agree with SbElectric. Avoiding a dead lock is not a matter of table design, but a matter of process/algorithms design. Saimadhu, could you please ask your dev team to explain how those tables are designed to avoid a dead lock, and tell us ? Thanks,
    85,865 pointsBadges:
  • Kccrosser
    The only way to make a database "deadlock proof" is to only allow one processing thread at a time to run. Hmmm... Or - I suppose you could theoretically build a database such that once any thread acquired ANY lock, no other thread could acquire ANY lock until that thread released all locks. I.e., all threads would be serialized at a system-wide lock level. That would be deadlock proof, but it would also be a very slow database... They may have configured the database to "detect" and resolve deadlocks (which means "kill and rollback" one of the deadlocking tasks), but there is no way to "design" a database to prevent deadlocks. By the use of good programming standards, you can minimize the occurrences of deadlocks. For example, if you require that all transactions that require two or more tables must acquire the locks on the tables in a particular order (e.g., by table name alphabetically), then as long as everyone follows the rules, you will not have deadlocks. Unless you control all multi-table transactions (or even multi-row transactions with row-level locking) using such methods, then any pair of threads can encounter a deadlock.
    3,830 pointsBadges:
  • Saimadhu
    Thanks for the replies. In our design, db tables are independent of each other. So no possibility of deadlock.
    455 pointsBadges:
  • SbElectric
    "In our design, db tables are independent of each other. So no possibility of deadlock." .... Hmmm.... you misssd the whole point of our discussion. Even if the db tables are independent of each other - there may be deadlock situation. Depends on number of users, task s(read/write/update) and many other environmental parameters. Need to have discussions with the development team & DBAs, DBMS system, and others.
    2,540 pointsBadges:
  • Kccrosser
    "db tables are independent of one another" Hmm... This creates a whole new thread of interest. If in fact the tables have no relationships between them, it isn't much of a database. That would be more like a collection of independent databases, each implemented as a single, flat, very wide row design. (Would you call that Zeroth Normal Form? <grin>) Note that you can create a deadlock with a database containing a single table of two rows: Transaction A updates row 1 Transaction B updates row 2 then tries to update row 1 (blocks due to unfinished Transaction A) Transaction A tries to update row 2 (blocks due to unfinished Transaction B) ==> deadlock Of course, I suppose you could escalate all locks to the database level, which would serialize all threads... Or if all tables really are independent, and no transaction ever accesses more than one table within the same transaction, then you could escalate all locks to the table level. However, both of these would produce exceptionally poor performance in any real application.
    3,830 pointsBadges:
  • Twlp123
    Author Dejan Sunderic, in his book "Microsoft SQL Server 2005- Stored Procedure programming in T-SQL and .Net" explains deadlock and gives a clear simulation of what can happen and provides the stored procedure script statements that will prevent the deadlock, on page 194-195. I'm assuming you dont have his book yet so I will extract the page from his book (for the sake of knowledge). "A deadlock occurs when two connections compete for resources at the same time and blocking each others completion. There are several types of deadlocks, but the textbook example occurs when: 1. connection 1 locks table A and changes it. 2. connection 2 locks table B and changes it. 3 connection 1 tries to acquire a lock on table B, but it has to wait until conection 2 completes its work. 4 connection 2 tries to acquire a lock on table A, bt it has to wait until connection 1 completes its work. 5 SQL server detects a deadlock and decides to kill one of the connection. Error 1502 is raised. 6 The other connection completes the transaction." Note the book contains two full pages of the example source code which will take me some time to type, but I can send it to you if you need it or you can refer to the book itself. The solution is to catch the error code 1502 in your error handling statement and then set a wait time delay of about a few seconds ( 3 secs maybe) and then set the procedure to retry again. By doing this you are just setting up a time overlap between the two connections. Lets face it, because its due to the process/algorithms design, deadlocks are unavoidable, but you can design your procedures to handle it. I'm not aware of any deadlock that can be avoided with table design, but if your design team can explain further, it will clear some doubts.
    165 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: