DML锁（TX、TM锁）和DDL锁（排他、共享、可中断解析锁）基础

来源：互联网发布：360网络电视在线直播编辑：程序博客网时间：2024/05/22 19:01

DML锁

DML锁用于确保一次只有一个人能修改一行，而且这时别人不能删除这个表。

1.TX锁

A row lock, also called a TX lock, is alock on a single row of table. A transaction acquires a row lock for each rowmodified by an INSERT, UPDATE, DELETE,MERGE, or SELECT ... FOR UPDATEstatement. The row lock exists until the transaction commits or rolls back.

一个行锁，也被称为TX lock，是对于表中一行的一个锁。当对行进行INSERT, UPDATE, DELETE,MERGE, or SELECT ... FOR UPDATE等操作时事务需要获取一个TX锁。这个锁会持续到事务提交或者rollback。

If a transaction obtains a lock for a row,then the transaction also acquires a lock for the table containing the row. Thetable lock prevents conflicting DDL operations that would override data changesin a current transaction.

可以防止DDL操作覆盖当前事务的数据。

复制EMP和DEPT表模拟实验。

EODA@PROD1> set lines 13EODA@PROD1> column username format a15EODA@PROD1> set echo on;EODA@PROD1> create table dept as select * from scott.dept;Table created.EODA@PROD1> create table emp as select * from scott.emp;Table created.EODA@PROD1> alter table dept add constraint dept_pk primary key(deptno);Table altered.EODA@PROD1> alter table emp add constraint emp_pk primary key(empno);Table altered.EODA@PROD1> alter table emp add constraint emp_fk_dept foreign key (deptno) references dept(deptno);Table altered.EODA@PROD1> create index emp_deptno_idx on emp(deptno);Index created.

--在第一个会话中执行

EODA@PROD1> select username,  2        v$lock.sid,  3        trunc(id1/power(2,16)) rbs,  4        bitand(id1,to_number('ffff','xxxx'))+0 slot,  5        id2 seq,  6        lmode,  7        request  8    from v$lock, v$session  9    where v$lock.type = 'TX' 10   and v$lock.sid = v$session.sid 11   and v$session.username = USER;USERNAME       SID  RBS    SLOTSEQ LMODE  REQUEST--------------- ---------- ---------- ---------- ---------- ---------- ----------EODA35   19      32588     60EODA@PROD1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;    XIDUSN    XIDSLOT  XIDSQN---------- ---------- ----------19   32     588

V$LOCK lists the locks currently held bythe Oracle Database and outstanding requests for a lock or latch.

V$LOCK常用列：

Column Datatype Description

ADDR RAW(4| 8) Address of lockstate object

KADDR RAW(4| 8) Address of lock

SID NUMBER Identifier for session holding or acquiring thelock --持有锁的会话SID

TYPE VARCHAR2(2) --主要为TM和TX两种类型

ID1 NUMBER Lock identifier #1 (depends on type) --当lock type 为TM时，id1为DML-lockedobject的object_id

ID2 NUMBER Lock identifier #2 (depends on type) --当lock type 为TX时，id1为usn+slot，而id2为seq。

LMODE NUMBER Lock mode in which the session holds the lock --大于0时表示当前会话以某种模式占有该锁，等于0时表示当前会话正在等待该锁资源，即表示该会话被阻塞。

REQUEST NUMBER Lock mode in which theprocess requests thelock

CTIME NUMBER Time since current mode was granted

BLOCK NUMBER Indicateswhether the lock in question is blocking otherprocesses.

Lock mode in which the session holds the lock:

0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

上述实验结果查询v$lock表时，LMODE为6,REQUEST为0表明该事务拥有一个排它锁。

--在第二个会话中执行

EODA@PROD1> update emp set ename = upper(ename);14 rows updated.EODA@PROD1> update dept set deptno = deptno-10;  --会话被阻塞，数据库hang住了

--再次查询v$lock

EODA@PROD1> select username,  2        v$lock.sid,  3        trunc(id1/power(2,16)) rbs,  4        bitand(id1,to_number('ffff','xxxx'))+0 slot,  5        id2 seq,  6        lmode,  7        request  8    from v$lock, v$session  9    where v$lock.type = 'TX' 10   and v$lock.sid = v$session.sid 11   and v$session.username = USER;USERNAME       SID  RBS    SLOTSEQ LMODE  REQUEST--------------- ---------- ---------- ---------- ---------- ---------- ----------EODA35   19      32588     60EODA45   19      32588     06EODA45   18      27583     60EODA@PROD1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;    XIDUSN    XIDSLOT  XIDSQN---------- ---------- ----------19   32     58818   27     583

通过查询v$transaction得出此时开始了一个新的事务(18,27,583)，SID为45的新会话在v$lock中有两行记录。首先拥有(18,27,583)排它锁其次在请求(19,32,588)即第一个会话的排它锁。

EODA@PROD1> col blocker format a15EODA@PROD1> col blockee format a15EODA@PROD1> select  2       (select username from v$session where sid=a.sid) blocker,  3        a.sid,  4       ' is blocking ',  5        (select username from v$session where sid=b.sid) blockee,  6    b.sid  7   from v$lock a, v$lock b  8  where a.block = 1  9    and b.request > 0 10    and a.id1 = b.id1 11    and a.id2 = b.id2;BLOCKER        SID 'ISBLOCKING'  BLOCKEESID--------------- ---------- ------------- --------------- ----------EODA35  is blocking  EODA 45

通过一个v$lock的自连接查询可以更明确看到哪个SID阻塞了哪个SID。

而如果提交了第一个事务，再次查询就可以看见请求不见了。

EODA@PROD1> commit;Commit complete.EODA@PROD1> select username,  2        v$lock.sid,  3        trunc(id1/power(2,16)) rbs,  4        bitand(id1,to_number('ffff','xxxx'))+0 slot,  5        id2 seq,  6        lmode,  7        request  8    from v$lock, v$session  9    where v$lock.type = 'TX' 10   and v$lock.sid = v$session.sid 11   and v$session.username = USER;USERNAME       SID  RBS    SLOTSEQ LMODE  REQUEST--------------- ---------- ---------- ---------- ---------- ---------- ----------EODA45   18      27583     60EODA@PROD1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;    XIDUSN    XIDSLOT  XIDSQN---------- ---------- ----------18   27     583

可以看到请求行已经不见了，因为另一个会话已经放弃了锁。

2.TM锁

TM锁用于确保在修改表的内容时，表的结构不会被改变。

A table lock, also called a TM lock, isacquired by a transaction when a table is modified by an INSERT, UPDATE,DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement. DMLoperations require table locks to reserve DML access to the table on behalf ofa transaction and to prevent DDL operations that would conflict with thetransaction.

一个表锁，也被称为TM锁，被用于一个事务中当表被通过INSERT, UPDATE, DELETE, MERGE, SELECT with theFOR UPDATE clause修改或者进行了LOCK TABLE操作。被用来防止DDL操作导致事务冲突影响了自身的事务。

--模拟观察

EODA@PROD1> set echo onEODA@PROD1> create table t1 ( x int );Table created.EODA@PROD1> create table t2 ( x int );Table created.EODA@PROD1> insert into t1 values ( 1 );1 row created.EODA@PROD1> insert into t2 values ( 1 );1 row created.EODA@PROD1> col username form a15EODA@PROD1> select (select username  2   from v$session  3  where sid = v$lock.sid) username,  4        sid,  5        id1,  6        id2,  7        lmode,  8        request, block, v$lock.type  9   from v$lock 10  where sid = sys_context('userenv','sid');USERNAME       SID  ID1     ID2      LMODE    REQUEST    BLOCK TY--------------- ---------- ---------- ---------- ---------- ---------- ---------- --EODA35  100       0  4     00 AEEODA3568045       1  3     00 TOEODA3585694       0  3     00 TMEODA3585695       0  3     00 TMEODA35     917523     583  6     00 TXEODA@PROD1> column object_name form a15EODA@PROD1> select object_name, object_id from user_objects where object_name in ('T1','T2');OBJECT_NAME OBJECT_ID--------------- ----------T1     85694T2     85695

虽然每个事务只能得到一个TX锁，但是TM锁并不一样，修改了多少个对象，就能得多少个TM锁。这里TM锁的ID1列就是DML锁定对象的对象ID，所以很容易发现哪个对象持有这个锁。

**关于TM锁还有另外一个有意思的地方，系统中允许的TM锁总数可以由你来进行配置，设置DML_LOCKS参数。

DML_LOCKS

Property Description

Parameter type Integer

Default value Derived: 4 * TRANSACTIONS

Modifiable No

Range of values 20 to unlimited; a setting of 0 disables enqueues

Basic No

OracleRAC You must set this parameter for every instance, and all instances must have positive values or all must be 0.

--如果该参数设置为0

If you set the value of DML_LOCKS to 0,enqueues are disabled and performance is slightly increased. However, youshould be aware of the following restrictions when you set you DML_LOCKS to 0:

You cannot use DROP TABLE, CREATE INDEX statements --不能进行删表，创建索引操作
EnterpriseManager cannot run on any instances for which DML_LOCKS is set to 0 --EM不能运行
You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE --不能显示锁定事务

在非常专业的应用比如RAC中，这一点很有用，可以减少实例内可能发生的协调次数。通过使用ALTER TABLE <> DISABLE TABLE LOCK命令还可以针对对象逐一禁用TM锁。

DDL锁

在DDL操作中会自动为对象加DDL锁，从而保护这些对象不会被其他会话所修改。在DDL语句执行期间会一直持有DDL锁，一旦操作执行完毕就会立即释放。

排他DDL锁（exclusive DDL lock）：这会防止其他会话得到他们自己的DDL锁或TM锁。这说明，在DDL操作期间可以查询一个表，但是无法以任何方式修改这个表。

共享DDL锁（share DDL lock）：这些锁会保护所引用的对象的结构，使之不会被其他会话修改，但是允许修改数据。

可中断解析锁（breakable parse lock）：这些锁允许一个对象向其他对象注册其依赖性。？？？

--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》

0 0