Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Transaction Deadlock

A deadlock  is a state in which two or more transactions wait at once for another transaction's lock to be released. CUBRID resolves the problem by rolling back one of the transactions because transactions in a deadlock state will hinder the work of another transaction. The transaction to be rolled back is usually the transaction which has made the least updates; it is usually the one that started more recently. As soon as a transaction is rolled back, the lock held by the transaction is released and other transactions in a deadlock are permitted to proceed.

It is impossible to predict such deadlocks, but it is recommended that you reduce the range to which lock is applied by setting the index, shortening the transaction, or setting the transaction isolation level as low in order to decrease such occurrences.

Note that if you configure the value of error_log_level, which indicates the severity level, to NOTIFICATION, information on lock is stored in error log file of server upon deadlock occurrences.

In the following error log file, (1) indicates a table name which causes deadlock state and (2) indicates an index name.

demodb_20111102_1811.err

    ...

    OID = -532| 520| 1

(1) Object type: Index key of class ( 0| 417| 7) = tbl.

    BTID = 0| 123| 530

(2) Index Name : i_tbl_col1

    Total mode of holders = NS_LOCK, Total mode of waiters = NULL_LOCK.

    Num holders= 1, Num blocked-holders= 0, Num waiters= 0

    LOCK HOLDERS:

    Tran_index = 2, Granted_mode = NS_LOCK, Count = 1

...

Example

session 1

session 2

;autocommit off

AUTOCOMMIT IS OFF

set transaction isolation level 6;

Isolation level set to:

SERIALIZABLE

;autocommit off

AUTOCOMMIT IS OFF

set transaction isolation level 6;

Isolation level set to:

SERIALIZABLE

CREATE TABLE lock_tbl(host_year integer, nation_code char(3));

INSERT INTO lock_tbl VALUES (2004, 'KOR');

INSERT INTO lock_tbl VALUES (2004, 'USA');

INSERT INTO lock_tbl VALUES (2004, 'GER');

INSERT INTO lock_tbl VALUES (2008, 'GER');

COMMIT;

 

SELECT * FROM lock_tbl;

    host_year  nation_code

===================================

         2004  'KOR'

         2004  'USA'

         2004  'GER'

         2008  'GER'

 

 

SELECT * FROM lock_tbl;

    host_year  nation_code

===================================

         2004  'KOR'

         2004  'USA'

         2004  'GER'

         2008  'GER'

DELETE FROM lock_tbl WHERE host_year=2008;

 

/* no result until transaction 2 releases a lock

 

C:CUBRID>cubrid lockdb demodb

*** Lock Table Dump ***

 

Object type: Class = lock_tbl.

LOCK HOLDERS:

    Tran_index =   2, Granted_mode =   S_LOCK, Count =   2, Nsubgranules =  0

 

BLOCKED LOCK HOLDERS:

    Tran_index =   1, Granted_mode =   S_LOCK, Count =   3, Nsubgranules =  0

    Blocked_mode = SIX_LOCK

    Start_waiting_at = Fri Feb 12 14:22:58 2010

    Wait_for_nsecs = -1

 

*/

 

 

INSERT INTO lock_tbl VALUES (2004, 'AUS');

ERROR: Your transaction (index 1, dba@ 090205|4760) has been unilaterally aborted by the system.

 

/*

System rolled back the transaction 1 to resolve a deadlock

 

C:CUBRID>cubrid lockdb demodb

*** Lock Table Dump ***

 

Object type: Class = lock_tbl.

 

LOCK HOLDERS:

    Tran_index =   2, Granted_mode = SIX_LOCK, Count =   3, Nsubgranules =  0

*/