Mysql系列 二 锁和事务隔离级别

什么事锁

       锁是计算机协调多个进程或线程并发访问某一资源的机制。

       在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
• 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

       读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

       写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

• 从对数据操作的粒度分，分为表锁和行锁

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

• 手动增加表锁：lock table 表名称 read(write),表名称2 read(write);
• 查看表上加过的锁：show open tables;
• 删除表锁：unlock tables;

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

1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

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

 

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB与MYISAM的最大不同有两点：

• 支持事务（TRANSACTION）
• 支持行级锁

行锁支持事务

事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

• 原子性(Atomicity)
• 一致性(Consistent)
• 隔离性(Isolation)
• 持久性(Durable)

并发事务处理带来的问题

更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。

 

脏读（Dirty Reads）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。

一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

 

不可重读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

 

幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

 

事务隔离级别

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

 

 

MVCC(Multiversion concurrency control 多版本并发控制)机制

   MVCC是为了解决什么问题?

   并发访问(读或写)数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。
　大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大，而MVCC可以在大多数情况下代替行级锁,使用MVCC，能降低其系统开销。

   MVCC实现

   MVCC是通过保存数据在某个时间点的快照来实现的。不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。当我们创建表完成后，mysql会自动为每个表添加 数据版本号（最后更新数据的事务id）db_trx_id 删除版本号 db_roll_pt （数据删除的事务id） 事务id由mysql数据库自动生成，且递增。

   插入 insert

  同一事务中（假设事务id=1）插入两条记录,记录的数据版本号为事务id=1,删除版本号为null

start transaction;（事务id为1）
INSERT INTO user (name,sex) VALUES ('张三','男');
INSERT INTO user (name,sex) VALUES ('李四','男');
commit;

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后

的第一个操作InnoDB 表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的

 

 

查询（select）

查询时需要同时满足以下两个条件：

1、查找数据版本号，早于（小于等于）当前事务id的数据行( 快照点已提交最大事务id)。 这样可以确保事务读取的数据是事务之前已经存在的。或者是当前事务插入或修改的。

2、查找删除版本号为null 或者大于当前事务版本号的记录。 这样确保取出来的数据在当前事务开启之前没有被删除。

 

　假如有一个事务中执行查询（假设事务id=2）

start transaction;（事务id为2）
select * from user where sex = '男';  --（1）
select * from user where sex = '男';  --（2）
commit;

   假设在执行这个事务ID为2的过程中,刚执行到(1)，这时有另一个事务（假设事务id=3）往这个表里插入了一条数据; 

start transaction;（事务id为3）
INSERT INTO user (name,sex) VALUES ('王五','男');
commit;

   此时表中的数据如下：

  然后接着执行事务 id=2 中的(2)，由于id=3的数据的创建时间(事务ID为3)，执行当前事务的ID为2，而InnoDB只会查找事务ID小于等于当前事务ID的数据行，所以 id=3 的数据行并不会在执行事务 id=2 中的 (2) 被检索出来。在事务 id=2 中的两条select 语句检索出来的数据都只会下表:

 

删除（delete）

如下图，假如有一个事务中执行查询（假设事务id=4）

start transaction;（事务id为4）
select * from user where sex = '男';  --（1）
select * from user where sex = '男';  --（2）
commit;

假设事务 id=4 刚执行到（1），此时有另外一个事务 id=5 执行了删除语句，会更新数据的删除版本号为当前事务id = 5　

start transaction;（事务id为5）
DELETE FROM user WHERE id = 1;
commit;

此时数据库表中数据如下：

接着执行事务 id=4的事务(2)，根据SELECT 检索条件可以知道，它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行，表中id=1的行由于删除时间(删除事务的ID)大于当前事务的ID，所以事务 id=2 的(2)在执行的时候也会把表中 id=1 的数据检索出来，所以事务4中的两条select 语句检索出来的数据都如下：

 

修改（update）

可以理解为，当一个事务中 修改一条记录时， 是先复制该数据，新数据数据版本号为当前事务id,删除版本号为 null 。然后更新 原来数据的删除版本号为 当前事务id。如下：

　　假如一个事务 id=6 执行了一条update语句:

start transaction;（事务id为6）
UPDATE user SET name='李四1' WHERE id = 2
commit;

      执行结果如下：