Comments on: How to test whether the Database tables designed to avoid deadlock occurence? Thu, 08 Dec 2016 19:10:14 +0000 hourly 1 By: twlp123 Wed, 10 Dec 2008 05:44:41 +0000 #comment-58469 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.

By: kccrosser Wed, 10 Dec 2008 00:57:07 +0000 #comment-58463 “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.

By: sbelectric Tue, 09 Dec 2008 20:02:27 +0000 #comment-58457 “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.

By: saimadhu Mon, 08 Dec 2008 12:52:17 +0000 #comment-58391 Thanks for the replies.

In our design, db tables are independent of each other.
So no possibility of deadlock.

By: kccrosser Wed, 03 Dec 2008 21:46:47 +0000 #comment-58309 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.

By: carlosdl Wed, 03 Dec 2008 20:42:31 +0000 #comment-58307 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 ?