Best Practices To Stop Deadlocks

Author: Vineela Sowjanya Sabbella

Src: https://cloudyard.in/2022/03/transaction-locks-in-snowflake/

PRIOR KNOWLEDGE:

Introduction to Transactions:

A transaction is a group that contains ordered SQL statements in a single unit. If we execute a transaction, all the SQL statements present inside it will get processed together and then executed.

Various transactions are specified below:

1. Explicit Transactions

2. Implicit Transactions

3. Scoped Transactions

Explicit Transaction:

The transaction must be started by a begin transaction or begin work command. The ending of the transaction is also a manual thing that is done by executing commit or rollback commands.

BEGIN [ { WORK | TRANSACTION } ] [ NAME <name> ]

If the transaction is initiated in the active state, then the begin transaction command is ignored.

The explicit transactions should only contain the DML or query statements. The DDL statement itself refers to the implicit transaction.

Implicit Transaction:

These implicit transactions are almost similar to explicit transactions, except these can start or end the transaction without using the COMMIT or ROLLBACK commands.

Here every DDL statement is taken as a new implicit transaction. Snowflake also supports the AUTOCOMMIT command. So, it is TRUE by default.

Scoped Transaction:

A scoped transaction is a transaction in which a stored procedure contains a transaction internally in which another stored procedure with another transaction is called.

Here is a small example to understand the above statement.

create procedure p1()
…
$$
begin transaction;
statement c;
statement d;
Commit;

$$

create procedure p2()
…
$$
begin transaction;
statement a;
statement b;

call p1();
Commit;

$$;

Resource Locking:

Transactions can lock the resources, such as tables or databases, while they are working on that resource. The locks will not allow other transactions to modify that resource until it is released.

Lock Wait Timeout:

The default value for any lock_timeout is 43200 seconds or 12 hours. If any transaction has locked any resource, the default time given to that transaction is 12 hours. After 12 hours, the lock will be automatically released on that particular resource.

We can change the default lock time by using ALTER command, alter session set lock_timeout=7200; show parameters like 'lock%';

Deadlocks

Deadlocks occur when two transactions are waiting for resources that are locked by each other.

Here is a use case that helps to understand the deadlock concept easily.

USE CASE:

Consider two transactions, Transaction1 and Transaction2, also the resources table1 and table2. Both transactions need both the resources to BEGIN WORK.

Case 1:

The transaction1 is in an active state and locks the resource on which it is working, i.e., table1 and next on table2. After some time, the operations of transaction1 are done, and the resource databases are released. Exactly at that time, transaction2 comes into the active state, and later, it completes its operations on both databases.

Case 2:

While transaction1 is working on table1, transaction2 also comes into an active state and works on table2. After some time, transaction1 needs table2. But the table2 is being locked by the transaction2.In this case, both transactions can't modify the other resource, which is in the opposite transaction lock. At this point, they start waiting for the other resource to be released. Therefore, the entire process takes more time to get executed compared to the time taken in case 1.

The default time for releasing a lock from a transaction is 12 hours. In case-2, the whole process to execute transaction2 takes 12 hours more than in case-1 .i.e., transaction1 takes 12 hours to release the table1.

Recommendations to avoid Deadlocks:

• Avoiding concurrent DML statements which involve deleting or updating against the same table reduces the deadlocks.

• Avoid overly large transactions

• Always set the parameter TRANSACTION_ABORT_ON_ERROR to true.

• Always keep AUTOCOMMIT to TRUE.

• Always set the LOCK_TIMEOUT value. The default value is 12 hours which is too large in many cases.

• Always set the STATEMENT_TIMEOUT_IN_SECONDS value. The default value is two days which is too large in most cases.

CONCLUSION:

In general, deadlocks occur very rarely in Snowflake as the AUTOCOMMIT is always set to TRUE. If they occur, we can avoid them by following the above best practices.

REFERENCE:

https://docs.snowflake.com/en/sql-reference/transactions.html