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 |
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 |
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 */ |
|