Handling DB2 Deadlocks in Batch Processing
By Tom Nonmacher
DB2 Deadlocks, a common issue in batch processing, can lead to significant processing delays and errors. Deadlocks occur when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. In this blog post, we will discuss how to handle these deadlocks in DB2 11.1, while also considering technologies like SQL Server 2016, SQL Server 2017, MySQL 5.7, and Azure SQL.
The first step in handling deadlocks is detection. In DB2, the system automatically detects deadlocks and chooses one of the tasks as a victim to resolve the deadlock. The victim's transaction is rolled back and an SQLCODE -911 is returned. In SQL Server, the system health extended events session can be used to monitor for deadlock events. In MySQL, you can monitor the SHOW ENGINE INNODB STATUS command output for deadlocks.
To demonstrate how to handle deadlocks, let's use a simple example in DB2. Consider two transactions T1 and T2 where T1 locks a row in table A and needs a row in table B while T2 locks a row in table B and needs a row in table A. This is a typical scenario for a deadlock.
-- Transaction T1
UPDATE A SET col1 = 'new value' WHERE col2 = 'old value';
-- Transaction T2
UPDATE B SET col1 = 'new value' WHERE col2 = 'old value';
The DB2 system can detect this deadlock and roll back one of the transactions to resolve it. However, the application needs to handle the SQLCODE -911 and decide whether to retry the transaction or not.
In SQL Server 2016 and 2017, you can use the TRY...CATCH construct to handle deadlocks. This allows you to catch the deadlock error and take appropriate action, such as retrying the transaction.
BEGIN TRY
-- Transaction code here
END TRY
BEGIN CATCH
-- Handle deadlock error here
END CATCH
In MySQL 5.7, you can use the SHOW ENGINE INNODB STATUS command to monitor for deadlocks and then handle them in the application code. Azure SQL also provides automatic deadlock detection and resolution, similar to DB2.
While automatic deadlock detection and resolution in DB2 and Azure SQL can help in handling deadlocks to some extent, it is important to design the database and transactions in such a way to minimize the occurrence of deadlocks. This can include ordering locks, reducing lock time, and using lower isolation levels.
In conclusion, handling DB2 deadlocks in batch processing involves a combination of system-provided deadlock detection and resolution, application-level error handling, and careful database and transaction design. By understanding how deadlocks occur and how to handle them, you can ensure smoother batch processing and better overall performance.