Lock it, Block it, but Don't Deadlock it

Are you looking to improve your database performance? If you've already taken care of the low-hanging fruit like database indexes, then deadlocks in your database could be the culprit.

Deadlocks create situations similar to a traffic jam at an intersection, where none of the cars can move. It occurs when two processes attempt to gain exclusive access to a resource, and each of them waits on the other to complete before moving forward.

In this article I'll discuss why deadlocks occur, how different databases handle them, how to resolve them, and tips on how to prevent them.

Looking for a modern SQL editor? Try Arctype's free SQL editor for easy queries, visualizations, and sharing.

Locking and Blocking

In this section, I'll discuss the concepts to understand why deadlocks occur.

Lock

Imagine that each row in your table has a "lock". In order to edit this row, the SQL statement would have to first acquire the lock. This prevents two SQL statements from updating the row at the same.

Locking protects data integrity during concurrent transactions. Relational databases take advantage of locking when running concurrent operations to prevent data inconsistency and data loss.

Block

This situation occurs when two processes (say Process A and Process B) need access to the same piece of data at the same point in time.

Suppose Process A has locked the piece of shared data. The other process, i.e., Process B waits for Process A to complete so that the lock on the shared piece of data is released. Process B is said to have been "blocked" by Process A.

Introduction to Deadlocks

Deadlock is a special blocking scenario that occurs when two processes are blocked by each other because they are attempting to access a resource that it locked by the other process.

Here's an example of two SQL statements that would cause a deadlock:

-- Process A
UPDATE Author SET Email = 'abcxyz@yahoo.com' WHERE AuthorId = 1
WAIT FOR DELAY '00:00:05'
UPDATE Book SET AuthorId = 1 WHERE BookId = 2

-- Process B
UPDATE Book SET BookCode = '2021-01' WHERE BookId = 2
WAITFOR DELAY '00:00:05'
UPDATE Author SET Phone = '1234567890' WHERE AuthorId = 1

Assuming both these transactions are executed simultaneously, Transaction A locks and updates the Author table. Transaction B, locks and updates the Book table. After a delay of 5ms, Transaction A attempts to acquire a lock on the Book table which is already held by Transaction B. Similarly, Transaction B attempts to acquire a lock on the Author table after 5ms while is already held by Transaction A.

Deadlock!

Neither transaction can complete until the other transaction is complete, creating a cyclic dependency where neither transaction holds onto the database resources in perpetuity.

How do Databases Handle Deadlock?

In this section I'll discuss the strategies different databases use to address database deadlock.

Deadlocks in SQL Server

SQL Server is adept at automatically detecting deadlocks and resolving them. When a deadlock occurs, SQL Server resolves it by automatically aborting one of the processes (this is also known as the "victim" process") and allowing the other process(s) to continue execution.

The aborted transaction (i.e., the “victim” process) is then rolled back. Lastly, SQL Server and sends an appropriate error message to the user of the process that has been aborted.

SQL Server runs a background process called Deadlock Monitor that auto-detects deadlocks. This background process runs after every 5 secs and checks if any deadlock has occurred. Hence a deadlock will not last longer than 5 secs. The "victim' process receives the deadlock error message and an error number of 1205.

To determine the "victim" process, SQL Server uses DEADLOCK_PRIORITY values. By setting the deadlock priority of a session as high, you can prevent a session from becoming a deadlock victim. Deadlock priority can be either LOW, NORMAL or HIGH, or a numeric value between -10 and 10.

SET DEADLOCK_PRIORITY HIGH
-- write your script here
SET DEADLOCK_PRIORITY 5

Deadlocks in Postgres and MySQL

Both MySQL and PostgreSQL can handle deadlocks gracefully. Both databases take advantage of Multiversion Concurrency Control (MVCC) to handle deadlocks.

In this strategy, when an item in the database has to be updated, the original data is not changed. Rather, a new version of the item together with the changes is created.

The biggest advantage of this approach is that the locks acquired for reading data will not conflict with the locks acquired for writing data. In essence, the reading process doesn't block the writing process and vice-versa.

But the trade-off is that your database could be storing multiple versions of the same records.

You can also programmatically lock specific rows for an UPDATE:

BEGIN;
SELECT 1 from Employee WHERE Id in (1,2,3,4) FOR UPDATE;
UPDATE Customer Set City = 'Hyderabad' WHERE name in ('Steve','Michael','Rod','Stephen');
END;

The statement SELECT...FOR UPDATE will acquire a lock on the relevant rows and release the lock after the transaction has completed.

Resolving Deadlocks in SQL Server - Implementing a Try / Retry Model

Above, I described how SQL Server chooses a victim process to fail in deadlock scenarios. But what if you want to guarantee that every process will complete? You can implement a Try / Retry model.

This is how it works:

1. When an exception has been thrown by SQL Server, check if the error number is 1205
2. Pause and let the other transaction complete its execution and release the locks it has acquired
3. Finally, resubmit the query pertaining to the "victim" process that was rolled back by SQL Server earlier

Let's rewrite the code for Transaction A and Transaction B to avoid deadlocks:

RETRY:
BEGIN TRANSACTION
BEGIN TRY

UPDATE Author SET Email = 'abcxyz@yahoo.com' WHERE AuthorId = 1
WAIT FOR DELAY '00:00:05'
UPDATE Book SET AuthorId = 1 WHERE BookId = 2

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205
BEGIN
WAITFOR DELAY '00:00:00.05'
GOTO RETRY
END
END CATCH

RETRY:
BEGIN TRANSACTION
BEGIN TRY
UPDATE Book SET BookCode = '2021-01' WHERE BookId = 2
WAITFOR DELAY '00:00:05'
UPDATE Author SET Phone = '1234567890' WHERE AuthorId = 1
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
IF ERROR_NUMBER() = 1205
BEGIN
WAITFOR DELAY '00:00:00.05'
GOTO RETRY
END
END CATCH

9 Tips to Prevent Deadlocks

1. Always try to keep transactions short
2. Avoid user input during transactions
3. Reduce the number of round trips between your application and the database
4. Ensure that your application acquires lock for the least possible time
5. Acquire locks as late as possible so that the queries can continue to execute for a longer period.
6. Avoid using cursors (SQL Server)
7. Avoid using default values in columns (some db engines will update every row and block the entire table)
8. Use lock timeouts to prevent processes from waiting too long for locks
9. Use create index concurrently in Postgres so you can use the table while the index is being built

Conclusion

In this article I covered:

• Locking and Blocking
• Deadlock scenarios
• How databases resolve deadlock
• Creating a try/retry model for SQL Server
• Tips for preventing deadlocks

Deadlocks are a naturally occurring situation as your database traffic increases, but now you're armed with strategies to address these situations.

If you're looking for a modern SQL editor that makes working with databases easier, try out Arctype today: