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

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 13
EODA@PROD1> column username format a15
EODA@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	    SLOT	SEQ	 LMODE	  REQUEST
--------------- ---------- ---------- ---------- ---------- ---------- ----------
EODA			35	   19	      32	588	     6		0

EODA@PROD1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

    XIDUSN    XIDSLOT	  XIDSQN
---------- ---------- ----------
	19	   32	     588

V$LOCK lists the locks currently held bythe Or