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 | 

Lock Mode Types And Compatibility

CUBRID determines the lock mode depending on the type of operation to be performed by the transaction, and determines whether or not to share the lock depending on the mode of the lock preoccupied by another transaction. Such decisions concerning the lock are made by the system automatically. Manual assignment by the user is not allowed. To check the lock information of CUBRID, use the cubrid lockdb db_name command. For details, see Checking Lock Status.

  • Shared lock (shared lock, S_LOCK): This lock is obtained before the read operation is executed on the object. It can be obtained by multiple transactions for the same object.
    Transaction T1 obtains the shared lock first before it performs the read operation on a certain object X, and releases it immediately after it completes the operation even before transaction T1 is committed. Here, transaction T2 and T3 can perform the read operation on  X concurrently, but not the update operation.
  • Exclusive lock (exclusive lock, X_LOCK): This lock is obtained before the update operation is executed on the object. It can only be obtained by one transaction.
    Transaction T1 obtains the exclusive lock first before it performs the update operation on a certain object X, and does not release it until transaction T1 is committed even after the update operation is completed. Therefore, transaction T2 and T3 cannot perform the read operation as well on X before transaction T1 releases the exclusive lock.
  • Update lock (update lock, U_LOCK): This lock is obtained when the read operation is executed in the expression before the update operation is performed.
    For example, when an UPDATE statement combined with a WHERE clause is executed, execute the operation by obtaining the update lock for each row and the exclusive lock only for the result rows that satisfy the condition when performing index search or full scan search in the WHERE clause. The update lock is converted to an exclusive lock when the actual update operation is performed. It can be called a quasi-exclusive lock because it does not allow read lock on the same object for another transaction.
  • Intention lock (intention lock): A lock that is set inherently in a higher-level object than X to protect the lock on the object X of a certain level.
    For example, when a shared lock is requested for a certain row, prevent a situation from occurring in which the table is locked by another transaction by setting the intention shared lock as well on the table at the higher level in hierarchy. Therefore, the intention lock is not set on rows at the lowest level, but is set on higher-level objects. The types of intention locks are as follows:
    • Intention shared lock (intention shared lock, IS_LOCK): If the intention shared lock is set on the table, which is the higher-level object, as a result of the shared lock set on a certain row, another transaction cannot perform operations such as changing the schema of the table (e.g. adding a column or changing the table name) or updating all rows. However updating some rows or viewing all rows is allowed.
    • Intention exclusive lock (intention exclusive lock, IX_LOCK): If the intention exclusive lock is set on the table, which is the higher-level object, as a result of the exclusive lock set on a certain row, another transaction cannot perform operations such as changing the schema of the table, updating or viewing all rows. However updating some rows is allowed.
    • Shared with intent exclusive (shared with intent exclusive, SIX_LOCK): This lock is set on the higher-level object inherently to protect the shared lock set on all objects at the lower hierarchical level and the intention exclusive lock on some object at the lower hierarchical level.
      Once the shared intention exclusive lock is set on a table, another transaction cannot change the schema of the table, update all/some rows or view all rows. However, viewing some rows is allowed.

The following table briefly shows the lock compatibility between the locks described below. Compatibility means that the lock requester can obtain a lock while the lock holder is keeping the lock obtained for the object X. N/a means 'not applicable'.

Lock Compatibility

 

Lock Holder

NULL_LOCK

IS_LOCK

S_LOCK

IX_LOCK

SIX_LOCK

U_LOCK

X_LOCK

Lock Requester
(lock requester)

NULL_LOCK

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

IS_LOCK

TRUE

TRUE

TRUE

TRUE

TRUE

N/A

FALSE

S_LOCK

TRUE

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

IX_LOCK

TRUE

TRUE

FALSE

TRUE

FALSE

N/A

FALSE

SIX_LOCK

TRUE

TRUE

FALSE

FALSE

FALSE

N/A

FALSE

U_LOCK

TRUE

N/A

TRUE

N/A

N/A

FALSE

FALSE

X_LOCK

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

  • NULL_LOCK: No lock
Example

session 1

session 2

;autocommit off

AUTOCOMMIT IS OFF

set transaction isolation level 4;

Isolation level set to:

REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

;autocommit off

AUTOCOMMIT IS OFF

set transaction isolation level 4;

Isolation level set to:

REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

 

/*

C:CUBRID>cubrid lockdb demodb

 

*** Lock Table Dump ***

 

Object Lock Table:

        Current number of objects which are locked    = 0

        Maximum number of objects which can be locked = 10000

*/

SELECT nation_code, gold FROM participant WHERE nation_code='USA';

 nation_code                  gold

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

'USA'                          36

'USA'                          37

'USA'                          44

'USA'                          37

'USA'                          36

 

/*

C:CUBRID>cubrid lockdb demodb

*** Lock Table Dump ***

 

Object type: Root class.

LOCK HOLDERS:

    Tran_index =   2, Granted_mode =  IS_LOCK, Count =   1, Nsubgranules =  1

 

Object type: Class = participant.

LOCK HOLDERS:

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

*/

 

 

UPDATE participant SET gold = 11 WHERE nation_code = 'USA' ;

SELECT nation_code, gold FROM participant WHERE nation_code='USA';

 

/* no results until transaction 2 releases a lock

 

C:CUBRID>cubrid lockdb demodb

*** Lock Table Dump ***

 

Object type: Instance of class ( 0|   551|   7) = participant.

LOCK HOLDERS:

    Tran_index =   3, Granted_mode =   X_LOCK, Count =   2

 

 

Object type: Root class.

LOCK HOLDERS:

    Tran_index =   3, Granted_mode =  IX_LOCK, Count =   1, Nsubgranules =  3

NON_2PL_RELEASED:

    Tran_index =   2, Non_2_phase_lock =  IS_LOCK

 

 

Object type: Class = participant.

LOCK HOLDERS:

    Tran_index =   3, Granted_mode =  IX_LOCK, Count =   3, Nsubgranules =  5

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

*/

 

 

COMMIT;

 

Current transaction has been committed.

nation_code                  gold

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

'USA'                          11           

'USA'                          11           

'USA'                          11           

'USA'                          11           

'USA'                          11           

 

/*

C:CUBRID>cubrid lockdb demodb

 

Object type: Root class.

LOCK HOLDERS:

    Tran_index =   2, Granted_mode =  IS_LOCK, Count =   1, Nsubgranules =  1

 

Object type: Class = participant.

LOCK HOLDERS:

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

*/

 

COMMIT;

 

Current transaction has been committed.

 

/*

C:CUBRID>cubrid lockdb demodb

 

Object Lock Table:

        Current number of objects which are locked    = 0

        Maximum number of objects which can be locked = 10000

*/