Oracle的事务和锁

一、什么是事务

必须具备以下四个属性，简称ACID 属性：
原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
一致性（Consistency）：一个查询的结果必须与数据库在查询开始时的状态保持一致（读不等待写，写不等待读）。
隔离性（Isolation）：对于其他会话来说，未完成的（也就是未提交的）事务必须不可见。
持久性（Durability）：事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。

二、事务的开始和结束

2.1 事务采用隐性的方式，起始于session的第一条DML语句，

2.2 事务结束于：
1）COMMIT（提交）或ROLLBACK（回滚）
2）DDL语句被执行（提交）
3）DCL语句被执行（提交）
4）用户退出SQLPLUS（正常退出是提交，非正常退出是回滚）
5）服务器故障或系统崩溃（回滚）
6）shutdowm immediate(回滚）

在一个事务里如果某个DML语句失败，之前其他任何DML语句将保持完好，而且不会提交！

三、Oracle 的事务保存点功能

savepoint命令允许在事务进行中设置一个标记（保存点），这个标记可以控制rollback的效果，即在一个事务中回滚掉最近的部分dml语句，保留下保存点之前的的dml语句，并使事务本身继续执行(考点）。也就是说回滚到保存点这个动作并不使事务结束。

SAVEPOINT实验
savepoint sp1;
delete from emp1 where empno=7900;
savepoint sp2;
update emp1 set ename=’timran’ where empno=7788;
select * from emp1；
rollback to sp2;
select * from emp1；
rollback to sp1;

//rollback to XXX 不会使事务结束。

四、SCN的概念

SCN全称是System Change Number
它是一个不断增长的整数，相当于Oracle内部的一个时钟，只要数据库一有变更，这个SCN就会+1，Oracle通过SCN记录数据库里事务的一致性。SCN涉及了实例恢复和介质恢复的核心概念，它几乎无处不在：控制文件，数据文件，日志文件都有SCN，包括block上也有SCN，

实际上，我们所说的保证同一时间点一致性读的概念，其背后是物理层面的block读，Oracle会依据你发出select命令，记录下那一刻的SCN值，然后以这个SCN值去同所读的每个block上的SCN比较，如果读到的块上的SCN大于select发出时记录的SCN，则需要利用Undo段，在内存中构造CR块(Consistent Read)。

得到当前SCN有两个办法：

SQL> conn / as sysdba
SQL> select current_scn from v$database;

CURRENT_SCN
———–
7222678

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
7222708

五、共享锁与排他锁的基本原理：

排他锁，排斥其他排他锁和共享锁。
共享锁，排斥其他排他锁，但不排斥其他共享锁。

因为有事务才有锁的概念。Oracle数据库锁可以分为以下几大类：

DML锁（data locks，数据锁），用于保护数据的完整性。
DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义。
SYSTEM锁（internal locks and latches），保护数据库的内部结构。

1）当一个用户对某表做DML操作时，也会加DDL锁，这样在事务未结束前，可防止另一个用户对该表做DDL操作。初始化参数ddl_lock_timeout可以设定了DDL锁的等待时间。时间过后如果事务仍未结束，则显示资源正忙。
2）当一个用户对某表做DDL操作时，也会加DML锁（EXCLUSIVE 排他锁），这样可以防止另一个用户对该表做DML操作

我们探讨的是Oracle的DML锁(又叫数据锁），它包括TM和TX两种

TM是面向对象的锁，它表示你锁定了系统中的一个对象，在锁定期间不允许其他人对这个对象做DDL操作。TM锁首先产生，目的就是为了实施DDL保护。
TX 是面向事务的锁，表示发起了一个事务，是否有事务产生，这是根据是否使用UNDO段作为评判标准的。

比如一个update语句，有表级锁（即TM)和行锁（即TX锁）。Oracle是先申请表级锁TM（其中的RX锁）, 获得后系统再自动申请行锁(TX), 并将实际锁定的数据行的锁标志置位（即指向该TX锁）。

对于DML操作

行锁(TX)只有一种
表锁(TM)共有五种，分别是 RS,RX,S,SRX,X。

六、五种TM表锁的含义：

ROW SHARE 行共享(RS)，允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁
ROW EXCLUSIVE 行排他(RX)，允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁
SHARE 共享(S)，不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁
SHARE ROW EXCLUSIVE(SRX) 共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁
EXCLUSIVE (X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何排他锁。

sql语句 加锁模式 许可其他用户的加锁模式
———————————————————————- ————————-
select * from table_name 无 RS,RX,S,SRX,X

insert, update, delete(DML操作） RX RS,RX

select * from table_name for update RX RS,RX

———————————————————————- ————————-

lock table table_name in row share mode RS RS,RX,S,SRX

lock table table_name in row exclusive mode RX RS,RX

lock table table_name in share mode S RS,S

lock table table_name in share row exclusive mode SRX RS

lock table table_name in exclusive mode X 无

七、加锁模式

第一种方式：自动加锁

做DML操作时，如insert，update，delete，以及select….for update由oracle自动完成加锁

session1 scott: //用for update加锁
SQL> select * from dept where deptno>20 for update;

DEPTNO DNAME LOC
———- ————– ————-
30 SALES CHICAGO
40 OPERATIONS BOSTON

session2 sys: //试探，以防被锁住
SQL>select * from scott.dept for update nowait;
SQL>select * from scott.dept for update wait 5;

session1 scott:
SQL> select * from emp where deptno=30 for update;

session2 sys: 跳过加锁的记录，锁定其他记录。
SQL> select * from scott.emp for update skip locked;

注意：
1）对整个表for update 是不锁insert语句的。
2）wait 5：等5秒自动退出。nowait：不等待。skip locked：跳过。都可起到防止自己被挂起的作用。

第二种方式：人工方式加锁，用lock命令以显式的方式加锁。

lock table 表名 in exclusive mode.（一般限于后三种表锁）

观察锁的动态视图v$lock
观察锁的静态视图dba_locks

select * from v$lock;

select * from dba_locks where session_id=149;

八、死锁和解锁

8.1 Oracle自动侦测死锁，自动解决锁争用。

制作死锁案例：

scott:

SQL> select * from a;

ID_A
———-
1
2

brain::

SQL> select * from b;

ID_B
———-
100
200

ORA-00060: deadlock detected while waiting for resource

scott: //改自己,不提交

update table a set id=11 where id=1;
brain: //改自己,不提交
update table b set id=1100 where id=100;
scott: //改对方,被锁住
update table brain.b id=1000 where id=100;
brain: //改对方,造成死锁
update table brain.b id=1000 where id=100;

8.2 管理员如何解鎖

可以根据以下方法准确定位要kill session的sid号和serial#号，

SQL> select * from v$lock where type in (‘TX’,’TM’);

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- —- ———- ———- ———- ———- ———- ———-
38B66D60 38B66D8C 127 TX 327680 1042 0 6 2985 0
00567BAC 00567BDC 134 TM 71090 0 3 0 2996 0
00567BAC 00567BDC 127 TM 71090 0 3 0 2985 0
37960894 379608D4 134 TX 327680 1042 6 0 2996 1

SQL> select a.sid,a.serial#,b.sql_text from v$session a,v$sql b where a.prev_sql_id=b.sql_id and a.sid=127;

SID SERIAL# SQL_TEXT
———- ———- ——————————————————————————–
127 2449 update emp1 set sal=8000 where empno=7788

SQL> select sid,serial#,blocking_session,username,event from v$session where blocking_session_status=’VALID';

SID SERIAL# BLOCKING_SESSION USERNAME EVENT
———- ———- —————- —————————— —————————————-
127 2449 134 SCOTT enq: TX – row lock contention

也可以根据v$lock视图的block 和request确定session阻塞关系,确定无误后再杀掉这个session

SQL>ALTER SYSTEM KILL SESSION ‘127,2449’;　

更详细的信息，可以从多个视图得出，相关的视图有：v$session,v$process,v$sql,v$locked,v$sqlarea等等

阻塞(排队）从OEM里看的更清楚 OEM–>Performance–>Additional Monitoring Links–>Blocking Sessions(或Instance Locks)