Lock types added to Oracle tables

The following table lists the types of locks added to the table and the compatibility between the locks and other types of operations.

Table 13-3 Summary of table locks

SQL statement	Mode of table lock	Lock modes permitted?
		RS	RX	S	SRX	X
SELECT...FROM table...	None	Y	Y	Y	Y	Y
INSERT INTO table...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y *	Y *	N	N	N
DELETE FROM table...	RX	Y *	Y *	N	N	N
SELECT ... FROM tableFOR UPDATE OF ...	RS (test as RX by yourself)	Y *	Y *	Y *	Y *	N
LOCK TABLE tableIN ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE tableIN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE tableIN SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE tableIN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N
LOCK TABLE tableIN EXCLUSIVE MODE	X	N	N	N	N	N

The relationship with the lmode field in V $ lock is as follows:

None: 0

RS: Row share: 2

RX: Row exclusive: 3

S: Share: 4

SRX: Share row exclusive: 5

X: exclusive: 6

1. The select operation will not apply any lock.

Select * from V $ lock where Sid = 'current session id' will not query the result.

2. insert into test values (3, 'C'); Add the RX lock to the table. View v $ lock, select * from V $ lock where Sid = 144

ADDR kaddr Sid ty id1 Id2 lmode request ctime Block
----------------------------------------------------------------------------------------
32e29c04 32e29d20 144 TX 458760 685 6 0 36 0
32dc4224 32dc423c 144 TM 54148 0 3 0 36 0

If the type is TM, the lock is applied to the table. Here, the lmode is 3, which corresponds to Rx. The block is 0, indicating that no other sessions are blocked.

Typeweitx indicates the lock applied to the row. Here, the lmode is 6, corresponding to X, and block is 0, indicating that other sessions are not blocked.

If the same record is updated in both sessions, the following result is returned:

ADDR kaddr Sid ty id1 Id2 lmode request ctime Block
----------------------------------------------------------------------------------------
32dc4224 32dc423c 144 TM 54148 0 3 0 132 0
32e29c04 32e29d20 144 TX 458760 685 6 0 132 1
32dc42e8 32dc4300 159 TM 54148 0 3 0 6 0
33834450 33834464 159 TX 458760 685 0 6 6 0

Sid 144 first. If block is 1, other sessions are blocked. Sid 159 TX lock lmode is 0, while request is 6, which means applying for the X lock, but not obtaining it, means it is blocked.

3. Lock table test in row share mode; apply the RS lock.

ADDR kaddr Sid ty id1 Id2 lmode request ctime Block
----------------------------------------------------------------------------------------
32dc4224 32dc423c 159 TM 54148 0 2 0 6 0

Execute select name from test where id = 3 for update of name; the results are different from those described in the document, and the results are also confusing:

Http://www.itpub.net/thread-1145275-1-1.html: Tests and queries found that, in fact, in Oracle 8i and 9I (earlier), select... for update produces indeed an RS lock.

But in later versions, the RX lock was generated. However, Oracle documents have not been updated until 10 Gb. This is very difficult.

ADDR kaddr Sid ty id1 Id2 lmode request ctime Block
----------------------------------------------------------------------------------------
32e29c04 32e29d20 144 TX 589869 828 6 0 6 0
32dc4224 32dc423c 144 TM 54148 0 3 0 6 0

4. Lock table test in share mode; s lock is applied. In addition, this lock will be added during index creation, which will block the update operation.

Of course, if the table has an update transaction, index cannot be created. At present, Oracle10g can use create index idx_id_test on test (ID) online to create indexes without the impact of transaction update.

ADDR kaddr Sid ty id1 Id2 lmode request ctime Block
----------------------------------------------------------------------------------------
32dc4224 32dc423c 144 TM 54148 0 4 0 3 0

Update Test Set Name = 'A' where id = 3 in another session. You will find that the apply for RX lock is blocked. At the same time, you can see that V $ lock has only two records,

This proves that this session does not apply for the X lock of TX after the application form lock is blocked.

ADDR kaddr Sid ty id1 Id2 lmode request ctime Block
----------------------------------------------------------------------------------------
32dc4224 32dc423c 144 TM 54148 0 4 0 42 1
32dc42e8 32dc4300 159 TM 54148 0 0 3 0

Submit the 144 session. We can see that the lmode of the 159 session is changed from 0 to 3, and an X lock of the Tx type is obtained.

ADDR kaddr Sid ty id1 Id2 lmode request ctime Block
----------------------------------------------------------------------------------------
32e0d0ac 32e0d1c8 159 TX 589842 829 6 0 3 0
32dc42e8 32dc4300 159 TM 54148 0 3 0 3 0

The lock TABLE statement only adds table-level locks, insert, update, delete, and select for update. the table-level locks are added first, and row-level locks are also applied.

DML statement	Row locks?	Mode of table lock
SELECT ... FROM table
INSERT INTO table...	X	RX
UPDATE table ...	X	RX
DELETE FROM table...	X	RX
SELECT ... FROM table...    FOR UPDATE OF ...	X	RS (test as RX by yourself)
LOCK TABLE tableIN ...
ROW SHARE MODE		RS
ROW EXCLUSIVE MODE		RX
SHARE MODE		S
SHARE EXCLUSIVE MODE		SRX
EXCLUSIVE MODE		X

DDL lock: When a DDL operation is performed, corresponding DDL lock will be added to the table it references. For example, a procedure cannot alter or drop the table it references during compilation. When alter is in progress, the table cannot be dropped.

The following are some frequently used lock-related SQL statements:

View the session lock information in the database. Block blocking for other processes is 1. The id1 and Id2 values of the blocked sessions are the same as those of the blocked sessions.

Select Sid, type, id1, Id2, block,
Decode (lmode, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row exclusive', 4, 'share ', 5, 'share row exclusive ', 6, 'clusive') lock_type,
Decode (request, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row exclusive', 4, 'share ', 5, 'share row exclusive ', 6, 'clusive') reqtype
From v $ lock order by SID, id1, Id2

Check which objects have locks. If the xidusn, xidslot, and xidsqn values are 0, they are blocked sessions.

Select rpad (oracle_username, 10) o_name, session_id Sid, object_name, xidusn, xidslot, xidsqn,
Decode (locked_mode, 0, 'none', 1, 'null', 2, 'row share ', 3, 'row exclusive', 4, 'share ', 5, 'share row exclusive ', 6, 'clusive') lock_type
From v $ locked_object, all_objects where V $ locked_object.object_id = all_objects.object_id order by object_name;

View blocked SQL statements:

Select st. SQL _text from V $ sqltext St, V $ session se where St. Address = Se. SQL _address and Se. Sid = 147 (147 is the SID of the blocked session)

You can get the SQL statement to kill the blocking process, and run the SQL statement to kill the session:

Select distinct 'alter system kill session '| CHR (39) | B. sid | ',' | B. serial # | CHR (39) | ';' As SQL _kill,
B. username, B. logon_time from V $ locked_object A, V $ session B
Where a. session_id = B. Sid and A. xidusn! = 0 order by B. logon_time

Remove a. xidusn! = 0 to get the SQL statements of kill-blocked and blocked sessions.