SQL之谈谈事务和锁 mysql事务和锁之间的关系

【十】事务和锁

10.1 事务具备的四个属性（简称ACID属性）：

1）原子性（Atomicity）：事务是一个完整的操作，事务的各步操作是不可分的（如原子不可分），操作要么都执行了，要么都不执行。

2）一致性（Consistency）：事务执行的结果必须使数据库从一个一致的状态到另一个一致的状态。

3）隔离性（Isolation）：事务的执行不干扰其他事务，一般来说数据库的隔离性都提供了不同程度的隔离级别。

4）持久性（Durability）：事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。

10.2 事务的隔离级别：

10.2.1事务可能出现的三种隔离问题：

1.脏读：
脏读是指在一个事务中读到了另一个事务未提交的记录。

2.不可重复读：

在一个事务还没有结束时，读到了另一个事务更改（update）的并提交的记录，两次重复读到同一记录的不同值。
3.幻读:
在一个事务还没有结束时，读到了另一个事务更改（insert）的并提交的记录，第二次读到了新的记录，发生了幻觉一样。

10.2.2采用SQL标准中的四个隔离级别，可以针对性的解决脏读、不可重复读、幻读这三类问题。

10.3 事务的开始和结束：

10.3.1 事务采用隐性的方式

1）起始于session的第一条DML语句；

2）一个session某个时刻只能有一个事务。

10.3.2 事务结束的方式：

1）用户退出SQLPLUS（正常退出是提交，非正常退出是回滚）；

2）服务器故障或系统崩溃（回滚）；

3）shutdowm immediate(回滚）。

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

4）在事务所在的会话中做非DML操作，比如做DDL操作，默认的是提交。

10.4 Oracle的事务保存点功能：

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 后，XXX左侧的事务不会结束。

10.5 SCN的概念

10.5.1概念：

SCN全称是System Change Number，它是一个不断增长的整数，相当于Oracle内部的一个时钟，只要数据库一有变更，这个SCN就会增加，Oracle通过SCN记录数据库里事务的一致性。

SCN涉及了实例恢复和介质恢复的核心概念，它几乎无处不在：控制文件，数据文件，日志文件都有SCN，包括block上也有SCN。

10.5.2理解多版本读一致性

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

10.5.3获得当前SCN的两个办法：

SQL> select current_scn from v$database;

SQL> select dbms_flashback.get_system_change_number from dual;

有两个函数可以实现SCN和TIMESTAMP之间的互转：

scn_to_timestamp / timestamp_to_scn

SQL> select scn_to_timestamp(current_scn) from v$database;

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

10.6.1 基本原则

排他锁：排斥其他的排他锁和共享锁；

共享锁：排斥其他的排他锁，不排斥其他的共享锁。

10.6.2 Oracle锁的种类

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

DML锁（data locks，数据锁），用于保护数据的完整性；

DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；

SYSTEM锁（internal locks and latches），保护数据库的内部结构。

我们探讨Oracle的DML操作（insert、update、delete），它包括两种锁：TX（行锁）和TM（表锁）。

TX 是面向事务的行锁，它表示你锁定了表中的一行或若干行，update和delete操作都会产生行锁，insert操作除外。

TM 是面向对象的表锁，它表示你锁定了系统中的一个对象，在锁定期间不允许其他会话对这个对象做DDL操作。否则会报“ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效”。（ddl_lock_timeout参数可以延迟一段时间再该报错）。

行锁(TX)只有一种，表锁(TM)共有五种：分别是 RS, RX, S, SRX, X。对于DML操作，Oracle会自动加表级锁（即TM中的RX锁)和行锁（即TX锁）。

10.7 五种TM表锁的含义：

1）ROW SHARE 行共享 (RS)

允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁

2）ROW EXCLUSIVE 行排他 (RX)

允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁

3）SHARE 共享 (S)

不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁

4）SHARE ROW EXCLUSIVE共享行排他 (SRX)

不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁

5）EXCLUSIVE排他 (X)

其他用户禁止更新任何行，禁止其他用户同时加任何排他锁。

10.8 加锁模式：

第一种方式：自动加锁

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

Session1/scott:

SQL> select * from dept1 where deptno=30 for update;

用for update加锁

Session2/sys:

SQL>select * from scott.dept1 for update;

不试探，被锁住

SSession2/sys:

SQL>select * from scott.dept1 for update nowait;

试探，以防被锁住

SQL>select * from scott.dept1 for update wait 5;

SQL> select * from scott.dept1 for update skip locked;

跳过加锁的记录，锁定其他记录

1）对整个表for update 是不锁insert语句的。

2）wait 5：等5秒自动退出；

nowait：不等待；

skip locked：跳过。

三个都可起到防止自己被挂起的作用。

【语法】：lock table 表名 in exclusive mode。（一般限于后三种表锁）

10.9 死锁和解锁：

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

制作死锁案例：

session1

update scott.emp1 set sal=8000 where empno=7369;

session2

update scott.emp1 set sal=9000 where empno=7934;

session1

update scott.emp1 set sal=8000 where empno=7934;

session2

update scott.emp1 set sal=9000 where empno=7369;

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

10.10 管理员如何解锁：

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

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

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等等…

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

the end ！！！

@jackman 共筑美好！