[Oracle] 锁(Lock)的探讨

Oracle当中的锁通常是业务层面的问题，锁是为了在并发的情况下保证数据库的一致性，因此没有并发就没有锁。Oracle的锁有如下几个

1、锁（Lock）的原则

Oracle当中的锁通常是业务层面的问题，锁是为了在并发的情况下保证数据库的一致性，因此没有并发就没有锁。Oracle的锁有如下几个原则：

2、TM锁和TX锁

TM 表锁，发生在insert,update,delete以及select for update操作时，目的是保证操作能够正常进行，并且阻止其它人对表执行DDL操作。

TX锁 事务锁（行锁）对于正在修改的数据，阻止其它会话进行修改。

TM锁和TX锁的对象不一样，，TM锁针对表，TX锁针对行，如下图所示，对某一行进行修改，加两个锁，其中一个是在修改的行上加TX锁，防止其它会话对该行的修改；另一个是在表上加TM锁，防止表DDL被修改。

 

 

3、TM锁的机种模式（lock mode）

Row Share (RS) --2

This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and
intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

Row Exclusive Table Lock (RX)---3

This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued
SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table.
Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.

Share Table Lock (S) --4

A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are
allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently,
holding this lock is not sufficient to ensure that a transaction can modify the table.

Share Row Exclusive Table Lock (SRX) ---5

This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can
acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR
UPDATE) but not to update the table.

Exclusive Table Lock (X) ---6

This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the
table.

 

上面摘自官方文档，比较抽象，下面这个表格更容易理解：

 

3、演示几种锁定的例子

首先，创建测试表t：

SQL> create table t (id int primary key);

 

表已创建。

两个session的id为：

session 1: 200

session 2: 202

 

1) Insert

在session 1中插入一条数据，不提交：

SQL> insert into t values (1);

 

已创建 1 行。

在session 2中插入同一条数据，阻塞发生：

SQL> insert into t values (1);

查看v$lock：

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by sid, type;

 

SID TY ID1 ID2 LMODE REQUEST BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

200 TM 74584 0 3 0 0

200 TX 524317 1048 6 0 1

202 TM 74584 0 3 0 0

202 TX 524317 1048 0 4 0

202 TX 65539 677 6 0 0

session 1在该行上持有mode=6的TX锁，session 2在该行上请求TX锁，导致它被阻塞。

注意：在11g中多了一个锁（看最后一行），这是一个TX锁，且ID1,ID2和前面的都一样，想不明白这个锁的用途是什么。

 

2）update

在session 1中更新id=1的那行，不提交：

SQL> update t set id=2 where id=1;

 

已更新 1 行。

在session 2中更新同一行数据，发生阻塞：

SQL> update t set id=3 where id=1;

查看v$lock：

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by sid, type;

 

SID TY ID1 ID2 LMODE REQUEST BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

200 TM 74584 0 3 0 0

200 TX 131092 822 6 0 1

202 TM 74584 0 3 0 0

202 TX 131092 822 0 6 0

 

3）Delete

在sesssion 1中删除一行，不提交：

SQL> delete from t where id=1;

 

已删除 1 行。

在session 2中删除同一行，发生阻塞：

SQL> delete from t where id=1;

查看v$lock：
 

4、RI锁---基于引用关系的锁定

当对具有主外键关系的表做DML操作时，锁定不单单发生在操作表上，相应的引用表上也可能加上相应的锁定。下面模拟RI锁定导致阻塞的场景：

分别创建主表和从表：

SQL> create table p(id int primary key);

 

表已创建。

 

SQL> create table c(id int references p(id));

 

表已创建。

在主表中插入一行数据：

SQL> insert into p values(1);

 

已创建 1 行。

查看v$lock:

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by sid, type;

 

SID TY ID1 ID2 LMODE REQUEST BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

200 TM 74587 0 3 0 0

200 TM 74589 0 3 0 0

200 TX 524299 1051 6 0 0

可以看出在主表和从表上都加了TM锁。

5、死锁 两个会话互相持有对方资源导致死锁。死锁的出现说明业务设计有问题，需要从业务逻辑上进行重新设计。

Oracle可以自动监控死锁，并强制让其中一个session释放资源的方式解决死锁问题，如：

 

在session 1中插入数据id=1:

SQL> insert into t values (1);

 

已创建 1 行。

 

在session 2中插入数据id=2：

SQL> insert into t values (2);

 

已创建 1 行。

 

再在session 1中输入数据id=2，阻塞发生：

SQL> insert into t values (2);

 

从v$lock中查看锁定情况：

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by sid, type;

 

SID TY ID1 ID2 LMODE REQUEST BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

200 TM 74584 0 3 0 0

200 TX 65550 687 0 4 0

200 TX 262176 648 6 0 0

202 TM 74584 0 3 0 0

202 TX 65550 687 6 0 1

 

再在session 2中插入数据id=1，死锁出现：

SQL> insert into t values (1);

 

Oracle在死锁出现后，自动释放其中一个session的资源，解决死锁问题：

SQL> insert into t values (2);

insert into t values (2)

*

第 1 行出现错误:

ORA-00060: 等待资源时检测到死锁

关于Lock和Latch的区别请看：