innodb mysql 锁事务 mysql事务加锁

【Mysql】（3）事务+锁

• （1）什么是事务？
• （2）事务的四大特性ACID
• （3）事务的创建过程
• （3）并发事务带来的数据问题
• （4）事务隔离级别（MySQL应对高并发事务是如何给出解决方案）

• （4.1）MySQL的默认隔离级别

• （5）锁机制与InnoDB锁算法

• （5.1）MyISAM和InnoDB存储引擎使用的锁：
• （5.2）表级锁和行级锁对比：
• （5.3）InnoDB存储引擎的锁的算法有三种：

• （6）锁

• （6.1）锁分类

• （6.1.1）按照锁对数据操作的粒度分类
• （6.1.2）按照是否可写分类
• （6.1.3）另外两个表级锁：IS和IX

• （6.2）死锁和避免死锁

• （7）三锁（开销、加锁速度、死锁、粒度、并发性能，只能根据具体应用的特点来说哪种锁更合适）

• （7.1）表锁（偏读）
• （7.2）行锁（偏写）

• （7.2.1）特点
• （7.2.2）行锁的事务支持
• （7.2.3）案例分析
• （7.2.4）行锁分析
• （7.2.5）优化建议

• （7.3）页锁（了解即可）

（1）什么是事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务由独立单元的一条或多条sql组成，单独单元不可分割，如果单元中某条sql语句执行失败或者产生错误，那么整个单元就会回滚，所有受到影响的数据都会返回到事务开始以前的状态。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

注意的是：MyISAM引擎是不支持事务的，也就是说不能使用transaction、commit、rollback等命令执行操作，但是可以使用表级锁模拟事务，也就是伪事务。

（2）事务的四大特性ACID

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；一致性主要由msql的日志机制处理，它记录数据库的所有变化，为事务回复提供跟踪记录。一致性属性保证数据库从不返回一个未处理的事务。
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；说白了，就是当前事务修改数据，在事务没提交之前，其他所有线程和事务都无法看到它的修改结果，保证事务和事务之间不会发生冲突
4. 持久性（Durability）： 一个事务被提交之后，数据库的日志已经被更新了，如果系统崩溃导致数据丢失，就可以使用日志里的记录和备份来恢复丢失的数据，例如我们自行导出的sql文件。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

（3）事务的创建过程

（1）初始化事务
start transaction;
这条指令开启了事务的一个单独单元。后面跟的是事务的sql语句
（2）创建事务
事务单元的具体执行内容，也就是要执行的所有sql语句。
（3）select检查一下数据是否被录入
检查一下要操作的数据是否已经发生了变化
（4）提交事务
commit指令用提交事务，标志着事务单独单元的结束
（5）事务回滚
rollback指令可以撤销事务单独单元里的所有执行结果，单元里的所有的sql都会执行回滚操作。

（3）并发事务带来的数据问题

多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题：

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

（4）事务隔离级别（MySQL应对高并发事务是如何给出解决方案）

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

（4.1）MySQL的默认隔离级别

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

（5）锁机制与InnoDB锁算法

（5.1）MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

（5.2）表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

（5.3）InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

（6）锁

（6.1）锁分类

（6.1.1）按照锁对数据操作的粒度分类

Mysql为了解决并发、数据安全的问题，使用了锁机制。可以按照锁的粒度把数据库锁分为表级锁和行级锁。

（1）表级锁：
Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

（2）行级锁：
Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种；

• Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
• Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
• Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：

• 事务更新大表中的大部分数据直接使用表级锁效率更高；
• 事务比较复杂，使用行级索很可能引起死锁导致回滚。

（6.1.2）按照是否可写分类

表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X）

（1）读锁：共享锁（s）
共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。
共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

（2）写锁：排他锁（X）
排它锁（(Exclusive lock,简记为X锁)）又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。

（3）两者之间的区别
1-共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不 能加排他锁。获取共享锁的事务只能读数据，不能修改数据。
2-排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获取排他锁的事务既能读数据，又能修改数据。

（6.1.3）另外两个表级锁：IS和IX

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

InnoDB另外的两个表级锁：

• 意向共享锁（IS）： 表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

注意：

• 这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。
• IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

（6.2）死锁和避免死锁

InnoDB的行级锁是基于索引实现的，如果查询语句为命中任何索引，那么InnoDB会使用表级锁. 此外，InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突，还需要注意的是，在通过

SELECT …LOCK IN SHARE MODE;

或

SELECT …FOR UPDATE;

使用锁的时候，如果表没有定义任何索引，那么InnoDB会创建一个隐藏的聚簇索引并使用这个索引来加记录锁。

此外，不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务，我们可以采取以上方式避免死锁：

• 通过表级锁来减少死锁产生的概率；
• 多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；
• 同一个事务尽可能做到一次锁定所需要的所有资源。

（7）三锁（开销、加锁速度、死锁、粒度、并发性能，只能根据具体应用的特点来说哪种锁更合适）

（7.1）表锁（偏读）

（1）特点
偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
（2）建表sql

create table mylock(
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;
 
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
 
select * from mylock;

（3）手动增加表锁：lock table 表名字1 read(write)，表名字2 read(write)，其它;
lock table mylock read, dept write;

（4）查看表上加过的锁

show open tables;

（5）释放表锁（全部表锁）

unlock tables;（6）加读锁

加读锁后自己可以读，自己不能写，自己不能读写其他表，他人可以读，他人写时等待锁

（7）加写锁

自己可以读，可以写

可以读写其他未加锁的表

其他表读写都需要等待锁释放

（8）结论

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）、表独占写锁（Table Write Lock）

锁类型	他人可读	他人可写
读锁	是	否
写锁	否	否

结合上表，所以对MyISAM表进行操作，会有以下情况：
1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

（7.2）行锁（偏写）

（7.2.1）特点

（7.2.2）行锁的事务支持

（1）事务和ACID
（2）并发事务处理带来的问题
（3）事务隔离级别

（7.2.3）案例分析

（1）建表sql

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;
 
insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');
 
create index test_innodb_a_ind on test_innodb_lock(a);
 
create index test_innodb_lock_b_ind on test_innodb_lock(b);
 
select * from test_innodb_lock;

（2）行锁定基本演示

（3）无索引行锁升级为表锁

（4）select也可以加锁

1-读锁

共享锁(Share Lock)：共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法：SELECT … LOCK IN SHARE MODE;

在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表（行？），而且这些线程读取的是同一个版本的数据。

2-写锁
排他锁（eXclusive Lock）：共享锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法：SELECT … FOR UPDATE;

在查询语句后面增加 FOR UPDATE ，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

（5）间隙锁危害
1-【什么是间隙锁】
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。

2-间隙锁带来的插入问题

3-【危害】

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

（6）结论
Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（7.2.4）行锁分析

【如何分析行锁定】
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
mysql>show status like ‘innodb_row_lock%’;

对各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是
Innodb_row_lock_time_avg（等待平均时长），
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

最后可以通过
SELECT * FROM information_schema.INNODB_TRX\G;
来查询正在被锁阻塞的sql语句。

（7.2.5）优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2. 尽可能较少检索条件，避免间隙锁
3. 尽量控制事务大小，减少锁定资源量和时间长度
4. 锁住某行后，尽量不要去调别的行或者表，赶紧处理被锁住的行，然后释放掉锁
5. 涉及相同表的事务，对于调用表的顺序尽量保持一致
6. 在业务环境允许的情况下，尽可能低级别事务隔离

（7.3）页锁（了解即可）