秋刀鱼在做梦

这个屌丝很懒，什么也没留下！

热门标签

mysql read-view_mysql事物隔离级别和传播行为本文作者：开发者首页

作者：秋刀鱼在做梦 | 2024-07-29 20:17:21

踩

mql事物叽隔离read

事务隔离级别

事务隔离级别的语义：当前事务执行过程中，通过select，update，delete 操作，对其他事务的影响，反过来也是如此，通俗的说就是当前事务是否可以看到其他事务的操作结果。

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

mysql 默认的隔离级别是：Repeatable read

如何查询当前数据库的隔离级别

select @@tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@global.tx_isolation;

设置

set tx_isolation=’read-committed’;

不同隔离级别的影响

ANSI/ISO SQL标准定义了4中事务隔离级别：未提交读(read uncommitted)，提交读(read committed)，重复读(repeatable read)，串行读(serializable)。

对于不同的事务，采用不同的隔离级别分别有不同的结果。不同的隔离级别有不同的现象。主要有下面3种现在：

1、脏读(dirty read)：一个事务可以读取另一个尚未提交事务的修改数据。

2、不可重复读(nonrepeatable read)：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了(update)，也可能被删除了(delete)。

3、幻像读(phantom read)：在同一事务中，同一查询多次进行时候，由于其他插入操作(insert)的事务提交，导致每次返回不同的结果集。

不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差，4种事务隔离级别分别表现的现象如下图：

AAffA0nNPuCLAAAAAElFTkSuQmCC

注意：

1、repeatable read 允许幻读，这是ANSI/ISO SQL标准的定义要求，运行幻读依然有非常大的隐患，mysql innodb引擎在repeatable read 即可满足没有幻读的要求。

2、不可重复读和幻读的区别：不可重复读的重点是修改，幻读的重点是插入或者删除了新数据。两都会造成系统错误，但是避免的方法则区别比较大，对于前者, 只需要锁住满足条件的记录，对于后者, 要锁住满足条件及其相近的记录。

Read uncommitted

这是事务最低的隔离级别，它充许令外一个事务可以看到这个事务未提交的数据。这种隔离级别会产生脏读，不可重复读和幻像读。

(1)所有事务都可以看到其他未提交事务的执行结果

(2)本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少

(3)该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据

63#首先，修改隔离级别

set tx_isolation='READ-UNCOMMITTED';

select @@tx_isolation;

+------------------+

| @@tx_isolation |

+------------------+

| READ-UNCOMMITTED |

+------------------+

#事务A：启动一个事务

start transaction;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：也启动一个事务(那么两个事务交叉了)

在事务B中执行更新语句，且不提交

start transaction;

update tx set num=10 where id=1;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：那么这时候事务A能看到这个更新了的数据吗?

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 | --->可以看到！说明我们读到了事务B还没有提交的数据

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：事务B回滚,仍然未提交

rollback;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：在事务A里面看到的也是B没有提交的数据

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 | --->脏读意味着我在这个事务中(A中)，事务B虽然没有提交，但它任何一条数据变化，我都可以看到！

| 2 | 2 |

| 3 | 3 |

+------+------+

Read committed

保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据

(1)这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)

(2)它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变

(3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

|——>导致这种情况的原因可能有：(1)有一个交叉的事务有新的commit，导致了数据的改变;(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit

55#首先修改隔离级别

set tx_isolation='read-committed';

select @@tx_isolation;

+----------------+

| @@tx_isolation |

+----------------+

| READ-COMMITTED |

+----------------+

#事务A：启动一个事务

start transaction;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：也启动一个事务(那么两个事务交叉了)

在这事务中更新数据，且未提交

start transaction;

update tx set num=10 where id=1;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：这个时候我们在事务A中能看到数据的变化吗?

select * from tx; --------------->

+------+------+ |

| id | num | |

+------+------+ |

| 1 | 1 |--->并不能看到！ |

| 2 | 2 | |

| 3 | 3 | |

+------+------+ |——>相同的select语句，结果却不一样

#事务B：如果提交了事务B呢? |

commit; |

#事务A: |

select * from tx; --------------->

+------+------+

| id | num |

+------+------+

| 1 | 10 |--->因为事务B已经提交了，所以在A中我们看到了数据变化

| 2 | 2 |

| 3 | 3 |

+------+------+

Repeatable read

这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻像读。它除了保证一个事务不能读取另一个事务未提交的数据外，还保证了避免下面的情况产生(不可重复读)。

(1)这是MySQL的默认事务隔离级别

(2)它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行

(3)此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行

(4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题

54#首先，更改隔离级别

set tx_isolation='repeatable-read';

select @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

#事务A：启动一个事务

start transaction;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务B：开启一个新事务(那么这两个事务交叉了)

在事务B中更新数据，并提交

start transaction;

update tx set num=10 where id=1;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

commit;

#事务A：这时候即使事务B已经提交了,但A能不能看到数据变化？

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 1 | --->还是看不到的！(这个级别2不一样，也说明级别3解决了不可重复读问题)

| 2 | 2 |

| 3 | 3 |

+------+------+

#事务A：只有当事务A也提交了，它才能够看到数据变化

commit;

select * from tx;

+------+------+

| id | num |

+------+------+

| 1 | 10 |

| 2 | 2 |

| 3 | 3 |

+------+------+

Serializable

这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻像读。

(1)这是最高的隔离级别

(2)它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。

(3)在这个级别，可能导致大量的超时现象和锁竞争

18#首先修改隔离界别

set tx_isolation='serializable';

select @@tx_isolation;

+----------------+

| @@tx_isolation |

+----------------+

| SERIALIZABLE |

+----------------+

#事务A：开启一个新事务

start transaction;

#事务B：在A没有commit之前，这个交叉事务是不能更改数据的

start transaction;

insert tx values('4','4');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

update tx set num=10 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

事物传播行为

PROPAGATION_REQUIRED

如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。

PROPAGATION_SUPPORTS

支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。‘

PROPAGATION_MANDATORY

支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。

PROPAGATION_REQUIRES_NEW

创建新事务，无论当前存不存在事务，都创建新事务。

PROPAGATION_NOT_SUPPORTED

以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

PROPAGATION_NEVER

以非事务方式执行，如果当前存在事务，则抛出异常。

PROPAGATION_NESTED

如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

Mysql(Innodb)如何避免幻读

幻读Phantom Rows

幻读问题是指一个事务的两次不同时间的相同查询返回了不同的的结果集。例如:一个 select 语句执行了两次，但是在第二次返回了第一次没有返回的行,那么这些行就是“phantom” row.

read view(或者说 MVCC)实现了一致性不锁定读(Consistent Nonlocking Reads)，从而避免了幻读

浅谈mysql mvcc

mysql的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，他们一般都同时实现了多版本并发控制，可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低，虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现是通过保存数据在某个时间点的快照来实现的，也就是说，不管需要执行多长时间，只要事务开始时间相同，每个事务看到的数据都是一致的，事务开始的时间不同时，每个事务对同一张表，同一时刻看到的数据可能是不一样的(因为不同的时间点可能数据就已经产生了不同的快照版本，而每个事务在默认的RR隔离级别下只能看到事务开始时的数据快照)。说道不同的存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制，下面简单说明MVCC是如何工作的：

例如：

此时books表中有5条数据，版本号为1

事务A，系统版本号2：select * from books；因为1<=2所以此时会读取5条数据。

事务B，系统版本号3：insert into books …，插入一条数据，新插入的数据版本号为3，而其他的数据的版本号仍然是2，插入完成之后commit，事务结束。

事务A，系统版本号2：再次select * from books；只能读取<=2的数据，事务B新插入的那条数据版本号为3，因此读不出来，解决了幻读的问题。

MVCC只在repeatable-read和read-committed两个隔离级别下才工作，其他两个隔离级别都和MVCC不兼容，因为read uncommitted总是读取最新的数据行，而不是符合当前事务版本的数据行，而serializeble则会对所有读取的行都加锁。

另外要注意：MVCC在RR和RC隔离级别下的区别，在RR隔离级别下，一个事务只能读取到事务开始的那个时刻的数据快照，即，别的事务修改并提交的数据在自身没有提交之前一般读取不到(加for update语句的select除外，因为这个语句要对数据加X锁必须读取最新的数据快照)，在RC隔离级别下，事务总是读取数据行的最新快照，即会产生不可重复读的问题。

repeatable-read 解决幻读还有一个特烈就是这个查询可以看到于自己开始之后的同一个事务产生的变化，这个特例会产生一些反常的现象。

repeatable-read 幻读特例：

71SESSION_A开始事务并创建快照

SESSION_A>START TRANSACTION WITH CONSISTENT SNAPSHOT;

Query OK, 0 rows affected (0.00 sec)

SESSION_B>begin;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;

+-------------------------+

| text |

+-------------------------+

| init |

| after session A select |

| before Session_A select |

+-------------------------+

3 rows in set (0.00 sec)

SESSION_B>insert into read_view values('anomaly'),('anomaly');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

SESSION_B>update read_view set text='INIT' where text='init';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

SESSION_B>commit;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;

+-------------------------+

| text |

+-------------------------+

| init |

| after session A select |

| before Session_A select |

+-------------------------+

3 rows in set (0.00 sec)

SESSION_A更新了它并没有"看"到的行

SESSION_A>update read_view set text='anomaly!' where text='anomaly';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

SESSION_A>select * from read_view;

+-------------------------+

| text |

+-------------------------+

| init |

| after session A select |

| before Session_A select |

| anomaly! |

+-------------------------+

5 rows in set (0.00 sec)

SESSION_A>commit;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;

+-------------------------+

| text |

+-------------------------+

| INIT |

| after session A select |

| before Session_A select |

| anomaly! |

+-------------------------+

5 rows in set (0.00 sec)

观察实验步骤可以发现，在倒数第二次查询中，出现了一个并不存在的状态

这里A的前后两次读，均为快照读，而且是在同一个事务中。但是B先插入直接提交，此时A再update，update属于当前读，所以可以作用于新插入的行，并且将修改行的当前版本号设为A的事务号，所以第二次的快照读，是可以读取到的，因为同事务号。这种情况符合MVCC的规则，如果要称为一种幻读也非不可，算为一个特殊情况来看待吧。

深层次的原理分析

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

MySQL/InnoDB定义的4种隔离级别：

Read Uncommited

可以读取未提交记录。此隔离级别，不会使用，忽略。

Read Committed (RC)

快照读(普通的select就是快照读)是不会对读取的行加锁的，所以存在幻读现象。

针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，加锁后的内容不允许update和delete，但是可以新增，所以存在幻读现象。

例如：

6事物1

SELECT * from qq for update ; 5条记录

事物2

delete from qq limit 1; commit；会阻塞因为上面一条语句有记录锁

事物1

update qq set bb=2 ; commit；会更新5条然后事物2执行完毕删除一条

例如存在幻读现象：

6事物1

SELECT * from qq for update ; 5条记录

事物2

insert into qq values(123456); commit；不会阻塞

事物1

update qq set bb=2 ; commit；会更新6条

Repeatable Read (RR)

快照读(普通的select就是快照读)是不会对读取的行加锁的，所以此情况还是会存在幻读现象。

针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

例如：

6事物1

SELECT * from qq for update ; 5条记录

事物2

insert into qq values(123456); commit；会阻塞因为上面一条语句有间隙锁

事物1

update qq set bb=2 ; commit；会更新5条然后执行事物2 新增一条

例如：

6事物1

SELECT * from qq for update ; 5条记录

事物2

delete from qq limit 1; commit；会阻塞因为上面一条语句有记录锁

事物1

update qq set bb=2 ; commit；会更新5条然后事物2执行完毕删除一条

Serializable

从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。

Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

InnoDB通过Nextkey lock解决了当前读时的幻读问题

Innodb行锁分为:

类型说明

Record Lock: 在索引上对单行记录加锁.

Gap Lock: 锁定一个范围的记录,但不包括记录本身.锁加在未使用的空闲空间上,可能是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间.

Next-Key Lock: 行锁与间隙锁组合起来用就叫做Next-Key Lock。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

实验1

创建表

6(mysql@localhost) [fandb]> create table t5(id int,key(id));

Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t5 values(1),(4),(7),(10);

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

开始实验

78SESSION_A>begin;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;

+------+

| id |

+------+

| 1 |

| 4 |

| 7 |

| 10 |

+------+

4 rows in set (0.00 sec)

SESSION_A>select * from t5 where id=7 for update;

+------+

| id |

+------+

| 7 |

+------+

1 row in set (0.00 sec)

SESSION_B>begin;

Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t5 values(2);

Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(12);

Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(5); --被阻塞

^CCtrl-C -- sending "KILL QUERY 93" to server ...

Ctrl-C -- query aborted.

^[[AERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(7); --被阻塞

^CCtrl-C -- sending "KILL QUERY 93" to server ...

Ctrl-C -- query aborted.

ERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(9); --被阻塞

^CCtrl-C -- sending "KILL QUERY 93" to server ...

Ctrl-C -- query aborted.

ERROR 1317 (70100): Query execution was interrupted

SESSION_B>commit;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;

+------+

| id |

+------+

| 1 |

| 4 |

| 7 |

| 10 |

+------+

4 rows in set (0.00 sec)

SESSION_A>commit;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;

+------+

| id |

+------+

| 1 |

| 2 |

| 4 |

| 7 |

| 10 |

| 12 |

+------+

6 rows in set (0.00 sec)

当以当前读模式select * from t5 where id=7 for update;获取 id=7的数据时,产生了 Next-Key Lock,锁住了4-10范围和 id=7单个record

从而阻塞了 SESSION_B在这个范围内插入数据，而在除此之外的范围内是可以插入数据的。

在倒数第二个查询中,因为 read view 的存在，避免了我们看到 2和12两条数据，避免了幻读

同时因为 Next-Key Lock 的存在,阻塞了其他回话插入数据，因此当前模式读不会产生幻读(select for update 是以当前读模式获取数据)

尽量使用唯一索引,因为唯一索引会把Next-Key Lock降级为Record Lock

实验2

创建表

6(mysql@localhost) [fandb]> create table t6(id int primary key);

Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t6 values(1),(4),(7),(10);

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

开始实验

61SESSION_A>begin;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;

+----+

| id |

+----+

| 1 |

| 4 |

| 7 |

| 10 |

+----+

4 rows in set (0.00 sec)

SESSION_A>select * from t6 where id=7 for update;

+----+

| id |

+----+

| 7 |

+----+

1 row in set (0.00 sec)

SESSION_B>begin;

Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t6 values(5); --插入成功没有阻塞

Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t6 values(8); --插入成功没有阻塞

Query OK, 1 row affected (0.00 sec)

SESSION_B>commit;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;

+----+

| id |

+----+

| 1 |

| 4 |

| 7 |

| 10 |

+----+

4 rows in set (0.00 sec)

SESSION_A>commit;

Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;

+----+

| id |

+----+

| 1 |

| 4 |

| 5 |

| 7 |

| 8 |

| 10 |

+----+

6 rows in set (0.00 sec)

当 id 列有唯一索引,Next-Key Lock 会降级为 Records Lock

InnoDB引擎的行锁和表锁

行锁和表锁

在mysql 的 InnoDB引擎支持行锁，与Oracle不同，mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，

行锁则无法实现，取而代之的是表锁。

表锁：不会出现死锁，发生锁冲突几率高，并发低。

行锁：会出现死锁，发生锁冲突几率低，并发高。

锁冲突：例如说事务A将某几行上锁后，事务B又对其上锁，锁不能共存否则会出现锁冲突。(但是共享锁可以共存，共享锁和排它锁不能共存，排它锁和排他锁也不可以)

死锁：例如说两个事务，事务A锁住了15行，同时事务B锁住了610行，此时事务A请求锁住610行，就会阻塞直到事务B施放610行的锁，而随后事务B又请求锁住15行，事务B也阻塞直到事务A释放15行的锁。死锁发生时，会产生Deadlock错误。

锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。

行锁的类型

行锁分共享锁和排它锁。

共享锁(Shared Lock，也叫S锁)

表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁，对于同一数据都能访问到数据，但是只能读不能修改。

产生共享锁的sql：select * from ad_plan lock in share mode;

共享锁的使用场景

SELECT … LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他人可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的过程执行完成(完成的情况有：事务的提交，事务的回滚，否则直接锁等待超时)。

SELECT … LOCK IN SHARE MODE的应用场景适合于两张表存在关系时的写操作，拿mysql官方文档的例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id) values (100)就不会存在这种问题了。

排他锁(Exclusive Lock，也叫X锁)

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。获取排他锁的事务是可以对数据就行读取和修改。

mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

产生排他锁的sql： select * from ad_plan for update;

排他锁的使用场景：

使用场景一：订单的商品数量

但是如果是同一张表的应用场景，举个例子，电商系统中计算一种商品的剩余数量，在产生订单之前需要确认商品数量>=1,产生订单之后应该将商品数量减1。

1 select amount from product where product_name=’XX’;

2 update product set amount=amount-1 where product_name=’XX’;

显然1的做法是是有问题，因为如果1查询出amount为1，但是这时正好其他session也买了该商品并产生了订单，那么amount就变成了0，那么这时第二步再执行就有问题。那么采用lock in share mode可行吗，也是不合理的，因为两个session同时锁定该行记录时，这时两个session再update时必然会产生死锁导致事务回滚。以下是操作范例(按时间顺序)

使用场景一：数据表的状态

如果存在一张表记录一个商品的状态，在订单的变化过程中，订单的状态是不断变化的，而且变化的过程中肯定也会有并发的问题，而且很多时候与其他系统有交互，会存在补偿的情况，所以并发的可能性很大,补偿或者为了增加状态修改的成功可能性，2次改变状态的情况也有，楼主就遇到了这种情况，真操蛋。于是看到有这样的for update写法。

1 update order set status = 1 where product_id = ‘1’;

2 insert order_flow (…………..) value (………)

这样的情况下就有可能订单的状态已经更新完成了，但是补偿这些额外的消息把状态又更新为待处理或者插入了多条流水的情况(多条流水的可能性大，状态的那种可能补偿滞后)。这个时候就可以使用select …. from order where order_id = ‘1’ for update，先锁住要修改状态的表，这样就不会别人操作了，自己先后面把流水插入，然后更新状态，完美。但是加了锁之后性能就很慢了，担心性能影响，而且有可能存在死锁的情况，后面我就修改为流水表中增加一个唯一索引，这样插入流水报错就是已经处理过的记录了。这样就不会存在性能问题。

通过对比，lock in share mode适用于两张表存在业务关系时的一致性要求，for update适用于操作同一张表时的一致性要求。

行锁注意几点

1.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

2.两个事务不能锁同一个索引，例如：

6事务A先执行：

select math from zje where math>60 for update;

事务B再执行：

select math from zje where math<60 for update；

这样的话，事务B是会阻塞的。如果事务B把 math索引换成其他索引就不会阻塞，但注意，换成其他索引锁住的行不能和math索引锁住的行有重复。

3.insert ，delete ， update在事务中都会自动默认加上排它锁。

实现：

7会话1：

begin；

select math from zje where math>60 for update；

会话2：

begin；

update zje set math=99 where math=68；

阻塞...........

会话相当与用户

如上，会话1先把zje表中math>60的行上排它锁。然后会话2试图把math=68的行进行修改，math=68处于math>60中，所以是已经被锁的，会话2进行操作时，

就会阻塞，等待会话1把锁释放。当commit时或者程序结束时，会释放锁。

mysql死锁

所谓死锁: 是指两个或两个以上的进程在执行过程中,

因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.

此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.

表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

死锁例子：

下面两个事物都执行了第一条update语句，更新了该数据同事锁定了该行数据接着每个事物都去执行第二天语句，却发现都被对方锁定，进入死循环。

9事物1

start TRANSACTION

update STOCKPRICE SET close = 89 where stock_id =4 and date = '2002-05-01'

update STOCKPRICE SET close = 22 where stock_id =3 and date = '2002-05-02'

事物2

start TRANSACTION

update STOCKPRICE SET close = 11 where stock_id =3 and date = '2002-05-02'

update STOCKPRICE SET close = 33 where stock_id =4 and date = '2002-05-01'

死锁解决办法：数据库实现了个各种死锁的检测和超时机制。

InnoDb可以检测到死锁的循环依赖，并返回一个错误，另一个就是查询时间达到锁超时时间设定后自动放弃锁请求。

如何尽可能避免死锁

1)以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。

2)大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3)在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4)降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5)为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

死锁查询

1、查询是否锁表

show OPEN TABLES where In_use > 0;

2、查询进程

show processlist

查询到相对应的进程===然后 kill id

3、查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

4、查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

猜你还想看

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/900411

mysql read-view_mysql事物隔离级别和传播行为 本文作者： 开发者首页

mysql read-view_mysql事物隔离级别和传播行为本文作者：开发者首页