Tutorial: Monitoring Deadlocks

In this tutorial, add deadlocks to the RLV store, log the deadlocks for reporting purposes, and report deadlock information using sa_report_deadlocks.

Prerequisites

SAP Sybase IQ server has a simplex database.
RLV storage is configured.

Task

This tutorial creates a cycle between two transactions in order to create the deadlock:

Transaction A will have lock A, and will then request lock B. Transaction B will have lock B and then attempt to request lock A.
Transaction A will block on the request for lock B, which will not be released until transaction B releases it.
At the same time, transaction B will request lock A, which will not be released until transaction A releases it.

These releases will never happen, since each transaction is waiting on a resource currently held by the other transaction. This is a classic deadlock scenario. The database server prevents user statements from inducing a deadlock scenario and automatically rolls back the transaction for the statement that introduced the deadlock.

Tip: You can monitor locks using SAP Control Center. See the SAP Control Center online help.

Create RLV-enabled tables rv_locks and rv_locks2, and configure table-level snapshot versioning.

SET TEMPORARY OPTION SNAPSHOT_VERSIONING = 'Table-level';

CREATE TABLE rv_locks(c1 INT, c2 INT, c3 INT);

INSERT INTO rv_locks VALUES (1,1,1);
INSERT INTO rv_locks VALUES (2,2,2);
INSERT INTO rv_locks VALUES (3,3,3);
INSERT INTO rv_locks VALUES (4,4,4);
COMMIT;

CREATE TABLE rv_locks2(c1 int, c2 int, c3 int);

INSERT INTO rv_locks2 VALUES (1,1,1);
INSERT INTO rv_locks2 VALUES (2,2,2);
INSERT INTO rv_locks2 VALUES (3,3,3);
INSERT INTO rv_locks2 VALUES (4,4,4);
COMMIT;

ALTER TABLE rv_locks ENABLE RLV STORE;
ALTER TABLE rv_locks2 ENABLE RLV STORE;

Use sp_iqlocks to ensure no row locks exist on the rv_locks and rv_locks2 tables.

Enable connection blocking and set the blocking timeout threshold.

set temporary option blocking = 'On';
set temporary option blocking_timeout = '0';

Each connection deletes a row.
- Connection A:
```
delete from rv_locks where c1 = 1
```
- Connection B:
```
delete from rv_locks2 where c1 = 1
```
These two DML actions begin the two separate transactions.
Execute sp_iqlocks.
```
sp_iqlocks
```

View the locks held by the two transactions.

Conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier,row_range
'SQL_DBC_13de5fd8',1,'DBA','BASE','DBA','rv_locks2',,'Schema','Transaction','Shared',,
'SQL_DBC_13de5fd8',1,'DBA','BASE','DBA','rv_locks2',,'Row','Transaction','Row',1,1
'SQL_DBC_13cd6038',3,'DBA','BASE','DBA','rv_locks',,'Schema','Transaction','Shared',,
'SQL_DBC_13cd6038',3,'DBA','BASE','DBA','rv_locks',,'Row','Transaction','Row',1,1
'RVL_CONN_T775',1000000407,'','BASE','DBA','rv_locks2',,'Table','Transaction','Intent',,
'RVL_CONN_T774',1000006141,'','BASE','DBA','rv_locks',,'Table','Transaction','Intent',,

In the output rows, note the conn_id's 1, and 3.

Connection A deletes the same row that Connection B already locked:
```
delete from rv_locks2 where c1 = 1
```
This connection blocks because Connection B already has the lock on that row.
Connection B tries to delete the same row that Connection A already locked:
```
delete from rv_locks where c1 = 1
```
The connection deadlocks. The database server recognizes the deadlock, and does not allow it to continue. The database server cancels this delete statement, rolls back the transaction, releases its locks, and issues this error message to Connection B's application: SQL error, state = 40001 “Deadlock Detected”.
Roll back the Connection A delete action.
- Connection A:
```
rollback
```
Enable deadlock logging.
- Connection B:
```
set option public.log_deadlocks = 'on';
```
Set logging for Connection B, since that is the connection that will induce the deadlock. Setting the log_deadlocks option for Connection A will not record any deadlocks.
Repeat the deadlock scenario.
- Connection A:
```
delete from rv_locks where c1 = 1
```
- Connection A:
```
delete from rv_locks2 where c1 = 1
```
- Connection B:
```
delete from rv_locks2 where c1 = 1
```
- Connection B:
```
delete from rv_locks where c1 = 1
```
Connection B receives an SQL error and its transaction is rolled back, as before. However, because deadlock logging is enabled, the system logged the deadlock event.
Execute sa_report_deadlocks.
```
sa_report_deadlocks
```

View the logged deadlock event.

snapshotId,snapshotAt,waiter,who,what,object_id,record_id,owner,is_victim,rollback_operation_count,iq_rid,iq_txn_id
1,2012-08-08 12:24:04.339,3,'DBA',delete from rv_locks2 where c1 = 1,1,775,1,false,0,1,13184
1,2012-08-08 12:24:04.339,1,'DBA',delete from rv_locks where c1 = 1,0,774,3,true,0,1,13160

The is_victim column indicates which transaction was selected as the rollback candidate.