Oracle table-level Lock (TM Lock)

Suppose a user (assuming a) issues the following statement to update a record:

Sql> Update Employees set last_name= ' Hansijie '

where employee_id=100;

Example above, at which point a user has issued an SQL statement that updates the record for employee_id 100. When a has not yet been submitted, another user D issues the following statement:

sql> drop table employees;

Because user A has not committed a transaction yet, the transaction has not ended, and other users cannot delete the table, otherwise the transaction issued by a will not end properly. To block the deletion of user D at this time, the most intuitive way we can think of is to check each record in the Employees table in turn before executing the command to delete it, to see if there is a lock on the head of each data row, and if so, the current transaction is updating the table. The operation to delete the table must wait.

Obviously, this approach can cause a lot of performance problems that Oracle does not use. In fact, when we update the data in the Employees table, we not only record row-level locks in the header of the data row, but also add a table-level lock at the table level. Then when the D user wants to delete the table, it is found that the Employees table has a table-level lock and waits.

By adding locks at the table level, we can easily and efficiently manage locks by not needing every record in the table to determine if there are DML transactions on the tables. Table-level locks altogether have five modes, as shown below.

Row-level exclusive locks (row Exclusive, short rx locks)

When we do DML, we automatically add an RX lock to the table being updated, or you can also explicitly add an RX lock to the table by executing the lock command. In this locking mode, other transactions are allowed to modify other rows of data in the same table through DML statements, or to add Rx locks to the same tables through the lock command, but do not allow other transactions to add exclusive locks (x locks) to the same table.

Row-Level shared locks (Row shared, abbreviated RS Lock)

is usually added through the select ... from for UPDATE statement, which is also the primary way we use to manually lock certain records. For example, when we query some records, we do not want other users to update the query's records, you can issue such a statement. When the data is used, the direct issue of the rollback command unlocks the lock. When an RS lock is added to a table, other transactions are not allowed to add exclusive locks to the same table, but other transactions are allowed to lock other rows of data in the same list through DML statements or lock commands.

Shared Lock (Share, abbreviation S lock)

Add the S lock through the lock table in share mode command. In this lock mode, no user is allowed to update the table. But allow other users to issue a select ... from the FOR UPDATE command to add RS locks to the table.

Exclusive Lock (Exclusive, short x Lock)

Add an X lock through the lock table in exclusive mode command. In this lock mode, other users cannot perform any DML and DDL operations on the table, and only queries can be made on the table.

Shared row-level exclusive locks (Share row Exclusive, short SRX Lock)

Add the SRX lock through the lock table in share row exclusive mode command. This locking mode is higher than row-level exclusive and shared-lock levels, and you cannot perform DML operations on the same table or add shared locks.

The compatibility of the TM locks in these five modes is shown in the following table (√ indicates mutually compatible requests; X indicates incompatible requests; N/a indicates no lock request):

-	S	X	Rs	Rx	SRX	N/A
S	√	X	√	X	X	√
X	X	X	X	X	X	√
Rs	√	X	√	√	√	√
Rx	X	X	√	√	X	√
SRX	X	X	√	X	X	√
N/A	√	√	√	√	√	√

As you can see from the previous description, not only can we automatically add TM locks at the table level by Oracle by issuing DML statements. We can also add a TM lock actively at the table level by issuing the lock Table command, where you can specify a different locking mode, with the command format as follows:

Lock table in [row Share][row exclusive]

[Share] [Share row Exclusive] [Exclusive] mode;

Summarizes the table-level locks produced by each SQL statement in an Oracle database, as shown in the following table:

rs, RX, S, SRX

sql statement	table lock modulo	table lock mode allowed
select * from ...	rs	rs, RX, S, SRX, X
insert into ...	rx	rs, RX
update ...	rx	rs, RX
delete from ...	rx	rs, RX
select * from to for update	rs
lock table in row share mode	rs	lock ta BLE in row exclusive mode
	rs, S
lock table in Sha Re row exclusive mode	rs
lock table in exclusive mode		rs

For locks that are actively added through the Lock Table command, you only need to issue the rollback command if you want to release them.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/