MySQL事务隔离级别和锁

MySQL 事务隔离级别 锁

1、数据库的锁

从性能上分为乐观锁和悲观锁：乐观锁是利用版本号，比如数据字段新增一个版本号字段，操作的时候进行版本的比对，需要开发者自己实现；悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，因此悲观锁需要耗费较多的时间。悲观锁是由数据库自己实现了的，执行CRUD操作都会涉及到。
从对对数据库的操作类型上面可以分为读锁和写锁，都属于悲观锁：读锁也叫共享锁，针对同一份数据，多个读操作可以同时进行，但是写操作不允许。写锁也叫排它锁，当写操作完成时，读写都不允许
从数据库的操作粒度上可以分为表锁和行锁：InnoDB支持行锁，myISAM不支持行锁。
表锁每次操作锁住整张表。开销小，加锁快（直接锁住整张表）；不会出现死锁；锁定粒度大，发生锁冲 突的概率最高，并发度最低；
行锁每次操作锁住一行数据。开销大，加锁慢（需要定位到那条数据）；会出现死锁（两个事务分别操作AB两条数据，事务一先操作B，再操作A，事务二先操作A，再操作B，这个时候就可能出现死锁）；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
加表锁：lock table 表1 read(write), 表2 read/write;
查看表锁：show open tables; 结果中In_use字段为1，表示加了表锁

释放当前会话加的表锁unlock tables;
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。InnoDB支持事务，因此它的锁和事务的隔离级别有一定关系。

2、数据库事务和隔离级别

2.1 事务

InnoDB支持事务而MyISAM是不支持事务的。
都知道事务具有四大属性——ACID。
原子性Atomicity：事务看做是一个原子操作，因此其对数据的修改，要么全都执行成功，要么都不成功。
一致性Consistent：事务开始和结束时的数据都是一致的 。
隔离性Isolation：事务处理中的数据状态对外部是不可见的，反之也无法获取到其他事务处理中的数据状态。
持久性Durable：事务提交之后，对数据的修改时永久性的。

2.2并发事务带来的问题

更新丢失：当多个事务更新同一行数据时，因为隔离性的存在，彼此都不知道其他的事务的存在，就会导致最后一个提交的事务，覆盖了其他事务提交的数据。
脏读：事务A正在对一条记录修改，事务B读了A正在修改的数据。但是此时事务A并未提交。后续因为问题事务A可能回滚，那么事务B读到的数据就是无效的脏数据。同时也破坏了事务的一致性的要求。
不可能重复读：在一次事务中，多次执行同样的查询条件，获取到的结果不一致，也就是读到了其他事务的修改的数据。不符合事务的隔离性
幻读：和不可重复读不一样的是幻读是读取到了新增的数据。
正因为存在以上问题，所以就就需要数据库提供一定的机制来解决这些问题。

2.3事务隔离级别

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用 对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力，所以大多数时候会采用的可重复读的事务隔离级别。

3、InnoDB的行锁

前面有说MyISAM会根据执行的操作对数据加表锁（读锁/写锁）。InnoDB有所不同，在非串行化的隔离级别下面select语句不会加锁，但是update，insert，delete会根据条件加行锁。且行锁是加在索引上面的，如果这些语句没有走索引，那么也会加表锁。
如果条件是范围，那么该范围内的所有行，包括每行记录所在的间隙区间都会被加上锁，就算该行数据还未被插入也会被加锁，这就是所谓的间隙锁，间隙锁在可重复读隔离级别下面才会生效。
比如说A表现有的id是1,2,3,4,5,10,20；那么间隙区间就有5-10,10-20,20-正无穷三个区间。执行update A set XXX= 'XXX' where id > 6 and id <16; 首先[6,16]这个范围的数据会被加锁，然6是落在5-10这个间隙区间的，这个区间的数据也会加锁，16是落在10-20这个间隙区间，这个区间的记录也会被加锁，所以执行上述sql时(5,16]左开又闭，这个区间的数据都会被加锁。如果执行的update A set XXX= 'XXX' where id > 6 and id <21；那么(5,正无穷) 都会被加锁，所以这个点还需要注意一下。
因此应该：

1. 尽可能让所有数据查找，修改都通过索引来完成，避免无索引行锁升级为表锁
2. 尽可能减少条件范围，缩小锁的范围，尽量避免间隙锁
3. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行