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.

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

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

*/