4

The scenario:

transaction A starts...

START TRANSACTION;
UPDATE table_name SET column_name=column_name+1 WHERE id = 1 LIMIT 1;

At the same time, transaction B starts...

START TRANSACTION;
UPDATE table_name SET column_name=column_name+1 WHERE id = 2 LIMIT 1;
UPDATE table_name SET column_name=column_name-1 WHERE id = 1 LIMIT 1;
COMMIT;

Right Now, transaction B is waiting for row 1, which is locked in transaction A.

And transaction A continues...

UPDATE table_name SET column_name=column_name-1 WHERE id = 2 LIMIT 1;
COMMIT;

And now we have a dead lock, so both transactions are waiting for each other to unlock a row that they want to update :'(

As I asked in the title, how can we prevent deadlocks in RDBMS transactions?

I think the only way to fix this situation is that we rollback transaction B and re-execute it. But how can we find out that we are in a deadlock, and get out of it immediately, and how can we guarantee that we do not stock in and endless loop (on very heavy web applications, for example).

If it is necessary, I'm using MySQL. But any solution for other RDBMSes are welcome - for helping other peoples coming here from Google :)

user2864740
  • 60,010
  • 15
  • 145
  • 220
user5483434
  • 492
  • 7
  • 17
  • You cannot prevent deadlocks, however, you can write code that minimises their occurance. – Shadow Feb 21 '16 at 00:25
  • 1
    @trincot I think it is, transaction A locks row #1, transaction B locks row #2 and waits for transaction A to unlock row #1, and transaction A waits for transaction B to unlock row #2, so both transactions are waiting for each other. Can you correct me if I'm wrong? Thanks. – user5483434 Feb 21 '16 at 00:28
  • 1
    @Shadow Can you please let me know how I can minimize their occurance? – user5483434 Feb 21 '16 at 00:29
  • You could write a book about that. Your question is way too broad, including so many rdbms do not help either. I'm voting to close this question. – Shadow Feb 21 '16 at 00:31
  • MySQL specific question - What is the [type of the table] (http://stackoverflow.com/questions/15678406/when-to-use-myisam-and-innodb), depending on what type it is you can use appropriate transaction isolation levels to run two transactions simultaneously. My guess is your table type is ISAM, and there is a table level lock and transaction B is waiting for transaction A to be complete, so that B can start execution. – SagaciousLearner Feb 21 '16 at 00:32
  • @SagaciousLearner To prevent whole table locks on inserts and updates, we are using InnoDB. Actually, all tables are InnoDB. – user5483434 Feb 21 '16 at 00:35
  • In that case please look at [Isolation Levels](https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html) using appropriate isolation level should help preventing row level locking. Recommend [SERIALIZABLE isolation level](https://msdn.microsoft.com/en-CA/library/ms173763.aspx) msdn explains the isolation level much better. – SagaciousLearner Feb 21 '16 at 00:52

1 Answers1

5

Most databases (if not all) will automatically detect a deadlock, pick one session to be the victim, and automatically roll back that session's transaction to break the deadlock. For example, here is the MySQL deadlock detection and rollback documentation.

Deadlocks are programming errors. One simple solution to avoiding deadlocks is to ensure that you always lock rows in a particular order. For example, if you have a transaction that wants to update two different rows, always update the row with the smaller id first and the larger id second. If your code always does that, you at least won't have row-level deadlocks. Beyond that, implement appropriate serialization for critical sections in your code. What, exactly, that entails is very dependent on your application.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you Justin. Actually I heard dead lock in [this question](http://stackoverflow.com/questions/35525208/is-it-ok-to-run-whole-php-application-in-a-mysql-transaction?noredirect=1#comment58747603_35525208) (that I asked a few hours ago). Can you help me on row-level deadlocks? Are there any other deadlocks in InnoDB? (table level? any other?) Also, English is not my mother tongue, can you please explain what you mean on "implement appropriate serialization for critical sections in your code" please? I'll read linked url now. Thanks again. – user5483434 Feb 21 '16 at 00:42
  • @user5483434 - The vast majority of deadlocks are row level deadlocks but there are any number of other resources that you could lock that could result in a deadlock. If you search for "concurrent programming deadlock", there are (many) books on concurrent programming and approaches to implementing appropriate serialization. That's not something that can realistically be covered in this sort of forum. And like I said, exactly what it entails depends heavily on your application. – Justin Cave Feb 21 '16 at 00:52