Nothing is impossible!

MySQL 学习笔记(二) MySQL-The Transaction and Lock of MySQL

Posted on By zhaofuchao

1. What is Transaction

什么是事务? 事务是访问数据库的一个操作序列,数据库通过事务来完成对数据的存取,事务的正确执行使得数据库从一种状态转换为另一种状态。

事务必须服从ISO/IEC所制定的ACID原则。

ACID是 原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)的缩写,这四种状态的意思是:

  • 1、原子性 即不可分割,事务要么全部被执行,要么全部不执行。如果事务的所有子事务全部提交成功,则所有的数据库操作被提交,数据库状态发生变化;如果有子事务失败,则其他子事务的数据库操作被回滚,即数据库回到事务执行前的状态,不会发生状态转换
  • 2、一致性 事务的执行使得数据库从一种正确状态转换成另外一种正确状态
  • 3、隔离性 在事务正确提交之前,不允许把事务对该数据的改变提供给任何其他事务,即在事务正确提交之前,它可能的结果不应该显示给其他事务
  • 4、持久性 事务正确提交之后,其结果将永远保存在数据库之中,即使在事务提交之后有了其他故障,事务的处理结果也会得到保存。

2.事务的作用及高并发下事务会产生的问题

2.1 事务的作用

事务管理对于企业级应用而言至关重要,它保证了用户的每一次操作都是可靠的,即便出现了异常的访问情况,也不至于破坏后台数据的完整性。就像银行的自动提款机ATM,通常ATM都可以正常为客户服务,但是也难免遇到操作过程中及其突然出故障的情况,此时,事务就必须确保出故障前对账户的操作不生效,就像用户刚才完全没有使用过ATM机一样,以保证用户和银行的利益都不受损失。

2.2 并发下事务会产生的问题

举个例子,事务A和事务B操纵的是同一个资源,事务A有若干个子事务,事务B也有若干个子事务,事务A和事务B在高并发的情况下,会出现各种各样的问题。”各种各样的问题”,总结一下主要就是五种:第一类丢失更新、第二类丢失更新、脏读、不可重复读、幻读。五种之中,第一类丢失更新、第二类丢失更新不重要,不讲了,讲一下脏读、不可重复读和幻读。

1、脏读 所谓脏读,就是指事务A读到了事务B还没有提交的数据,比如银行取钱,事务A开启事务,此时切换到事务B,事务B开启事务–>取走100元,此时切换回事务A,事务A读取的肯定是数据库里面的原始数据,因为事务B取走了100块钱,并没有提交,数据库里面的账务余额肯定还是原始余额,这就是脏读。

2、不可重复读 所谓不可重复读,就是指在一个事务里面读取了两次某个数据,读出来的数据不一致。还是以银行取钱为例,事务A开启事务–>查出银行卡余额为1000元,此时切换到事务B事务B开启事务–>事务B取走100元–>提交,数据库里面余额变为900元,此时切换回事务A,事务A再查一次查出账户余额为900元,这样对事务A而言,在同一个事务内两次读取账户余额数据不一致,这就是不可重复读。 3、幻读 所谓幻读,就是指在一个事务里面的操作中发现了未被操作的数据。比如学生信息,事务A开启事务–>修改所有学生当天签到状况为false,此时切换到事务B,事务B开启事务–>事务B插入了一条学生数据,此时切换回事务A,事务A提交的时候发现了一条自己没有修改过的数据,这就是幻读,就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。

3. 事务隔离级别

事务隔离级别,就是为了解决上面几种问题而诞生的。为什么要有事务隔离级别,因为事务隔离级别越高,在并发下会产生的问题就越少,但同时付出的性能消耗也将越大,因此很多时候必须在并发性和性能之间做一个权衡。所以设立了几种事务隔离级别,以便让不同的项目可以根据自己项目的并发情况选择合适的事务隔离级别,对于在事务隔离级别之外会产生的并发问题,在代码中做补偿。 事务隔离级别有4种,但是像Spring会提供给用户5种,来看一下:

1、DEFAULT() 默认隔离级别,每种数据库支持的事务隔离级别不一样,如果Spring配置事务时将isolation设置为这个值的话,那么将使用底层数据库的默认事务隔离级别。顺便说一句,如果使用的MySQL,可以使用”select @@tx_isolation”来查看默认的事务隔离级别。

2、READ_UNCOMMITTED(读取未提交) 读未提交,即能够读取到没有被提交的数据,所以很明显这个级别的隔离机制无法解决脏读、不可重复读、幻读中的任何一种,因此很少使用。

3、READ_COMMITED(读取已提交) 读已提交,即能够读到那些已经提交的数据,自然能够防止脏读,但是无法限制不可重复读和幻读。

4、REPEATABLE_READ(可重复读) 重复读取,即在数据读出来之后加锁,类似”select * from XXX for update”,明确数据读取出来就是为了更新用的,所以要加一把锁,防止别人修改它。REPEATABLE_READ的意思也类似,读取了一条数据,这个事务不结束,别的事务就不可以改这条记录,这样就解决了脏读、不可重复读的问题,但是幻读的问题还是无法解决。

5、SERLALIZABLE(串行化) 串行化,最高的事务隔离级别,不管多少事务,挨个运行完一个事务的所有子事务之后才可以执行另外一个事务里面的所有子事务,这样就解决了脏读、不可重复读和幻读的问题了

网上专门有图用表格的形式列出了事务隔离级别解决的并发问题: 如下图为事务的级别对应的问题:

再必须强调一遍,不是事务隔离级别设置得越高越好,事务隔离级别设置得越高,意味着势必要花手段去加锁用以保证事务的正确性,那么效率就要降低,因此实际开发中往往要在效率和并发正确性之间做一个取舍,一般情况下会设置为READ_COMMITED,此时避免了脏读,并发性也还不错,之后再通过一些别的手段去解决不可重复读和幻读的问题就好了。

事务相关sql语句:

查看数据库事务隔离级别:SELECT @@tx_isolation;

设置事务隔离级别:set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED READ COMMIT REPEATABLE READ SERIALIZABLE;
开启一个事务:START TRANSACTION; begin;

提交一个事务:COMMIT;

回滚一个事务:ROLLBACK;

4. 锁

悲观锁(Pessimistic Lock)、乐观锁(Optimistic Lock)、共享锁、排它锁、行锁、表锁概念的理解

4.1 乐观锁(Optimistic Lock)

乐观锁不是数据库自带的,需要我们自己去实现。乐观锁是指操作数据库时(更新操作),想法很乐观,认为这次的操作不会导致冲突,在操作数据时,并不进行任何其他的特殊处理(也就是不加锁),而在进行更新后,再去判断是否有冲突了。

通常实现是这样的:在表中的数据进行操作时(更新),先给数据表加一个版本(version)字段,每操作一次,将那条记录的版本号加1。也就是先查询出那条记录,获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等,如果相等,则说明这段期间,没有其他程序对其进行操作,则可以执行更新,将version字段的值加1;如果更新时发现此刻的version值与刚刚获取出来的version的值不相等,则说明这段期间已经有其他程序对其进行操作了,则不进行更新操作。

4.2 悲观锁(Pessimistic Lock)

悲观锁的特点是先获取锁,再进行业务操作,即“悲观”的认为获取锁是非常有可能失败的,因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持,即通过常用的select … for update操作来实现悲观锁。

这点跟java中的synchronized很相似,所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了(即在sql语句后面加上 for update)。

乐观锁和悲观锁如何实现,可参看如下博客: https://www.cnblogs.com/cyhbyw/p/8869855.html

4.3 共享锁

共享锁指的就是对于多个不同的事务,对同一个资源共享同一个锁。相当于对于同一把门,它拥有多个钥匙一样。就像这样,你家有一个大门,大门的钥匙有好几把,你有一把,你女朋友有一把,你们都可能通过这把钥匙进入你们家,这个就是所谓的共享锁。

对于悲观锁,一般数据库已经实现了,共享锁也属于悲观锁的一种,那么共享锁在mysql中是通过什么命令来调用呢。通过查询资料,了解到通过在执行语句后面加上lock in share mode就代表对某些资源加上共享锁了。

共享锁只支持读操作,多个事务同时select 同一条数据时可以生效,但是对于增删改则不行,对于update,insert,delete语句会自动加排它锁的

4.4 排它锁

排他锁 exclusive lock(也叫writer lock)又称写锁。 排它锁与共享锁相对应,就是指对于多个不同的事务,对同一个资源只能有一把锁。

与共享锁类型,在需要执行的语句后面加上for update就可以了。

4.5 行锁与表锁

行锁 行锁又分共享锁和排他锁,由字面意思理解,就是给某一行加上锁,也就是一条记录加上锁。

注意:行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁。

表锁 如何加表锁 innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

Innodb中的行锁与表锁 前面提到过,在Innodb引擎中既支持行锁也支持表锁,那么什么时候会锁住整张表,什么时候或只锁住一行呢?

只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。

行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁。行级锁的缺点是:由于需要请求大量的锁资源,所以速度慢,内存消耗大。

4.6 死锁(Deadlock)

所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法:

第一种:

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程(如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程)

show processlist

3.杀死进程id(就是上面命令的id列)

kill id

第二种:

1:查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2:查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3:查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

4:杀死进程

kill 线程ID

下列方法有助于最大限度地降低死锁:

(1)按同一顺序访问对象。

(2)避免事务中的用户交互。

(3)保持事务简短并在一个批处理中。

(4)使用低隔离级别。

(5)使用绑定连接。