mysql专题锁机制

　　mysql专题 - 锁机制
　　无论是工作还是面试我们都有很多可能性接触mysql。我打算好好梳理一下Mysql的基础，因此打算开启一个专题。有不对的地方也麻烦指正。
　　mysql锁进行划分 按照锁的粒度划分：行锁、表锁、页锁 两种思想上的锁：悲观锁、乐观锁。 InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key LockRecord Lock：在索引记录上加锁Gap Lock：间隙锁Next-key Lock：Record Lock+Gap Lock 行锁
　　行级锁是颗粒度最细的一种锁，表示对当前操作的行进行加锁。能减少数据操作的冲突，但是加锁开销最大，有可能会出现死锁。行级锁按照使用方式分为共享锁和排他锁。 共享锁（读锁 S锁）事务A对表 T的某一行R进行了（ 以下简称TR ） S锁操作，这个时候其他事务只能对 TR加S锁不能加X锁（X锁在下面）。这保证了其他事务可以读，但在A释放TR上的S锁之前不能对TR做任何修改。 -- 加 S 锁示例
　　SELECT * FROM tmp_table WHERE id = ＂3＂ LOCK IN SHARE MODE; 排它锁（写锁 X锁）事务A对表 TR 做了X锁操作，这个时候其他事务不能对 TR加锁。这保证了其他事务在A释放TR上的锁之前不能再读取和修改TR。排它锁也成为独占锁 -- 加 X 锁
　　SELECT * FROM tmp_table WHERE id = ＂3＂ for update;
　　行级锁是如何引起死锁的呢？
　　单表操作的时候是不会引起死锁的，当存在多表关联的时候就有可能产生死锁。 -- 伪代码，  -- 事务1开始 select * from tmp_table where id = ＂1＂ for update; select * from tmpA a,tmp_table2 b where a.name = b.name; -- 事务1结束  -- 事务2开始 select * from tmp_table2 where id = ＂1＂ for update; select * from tmpA a,tmp_table1 b where a.name = b.name; -- 事务2结束表锁
　　表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁。这种锁开销小，不会死锁，但是很容易发生数据操作的冲突。大部分的mysql引擎都支持。MyISAM和InnoDB都支持表级锁，InnoDB默认的是行级锁。
　　同样的表锁也分为共享锁和排他锁 --  共享锁 读锁 LOCK TABLE tmp_table READ; -- 排它锁 写锁 LOCK TABLE tmp_table WRITE; -- 解锁 unlock tables;页锁
　　页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多。一次锁定相邻的一组记录。BDB支持页级锁。目前我工作中很少是哟个 BDB引擎，因此这部分就不看如何加的了，了解个大概就可以 乐观锁和悲观锁
　　首先客观所和悲观锁并仅仅是数据库的概念，很多地方都会有这种概念。
　　悲观锁总是把事情想的悲观，在处理数据的时候会认为其他事务会处理自己要处理的数据，就有可能产生错误。为了防止这种情况发生，因此悲观锁在处理数据的时候都是先加锁，加锁成功后在处理自己要处理的事务
　　乐观锁则相反，在处理数据的时候乐观的认为不会有其他事务来处理数据，因此先进行数据处理，在最终提交数据的时候检查数据是否冲突。
　　从上面的解释来看 悲观锁效率较低（因此持有锁的时间比较长），而且也可能产生死锁，降低了并发量，但是保证了数据的准确性 乐观锁不会产生死锁，相对效率也较高，但是有可能会产生脏数据
　　看下mysql中如何模拟悲观锁 -- 取消mysql事务自动提交 set autocommit=0; -- 开始事务 (三者选一就可以) begin;/begin work;/start transaction; -- 加锁操作及事务操作 select ... for update; -- 提交事务 commit;/commit work;   -- 窗口1 set autocommit=0; -- 锁表 select * from tmp_table for update;   -- 窗口2： -- 会显示等待状态，一直到窗口1执行commit提交事务才会出现下面的显示结果 update tmp_table set tmp6=＂3＂ WHERE id = ＂3＂ ;InnoDB中的锁
　　InnoDB锁的特性 在不通过索引条件查询的时候，InnoDB使用的是表锁 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。 表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。 即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。 Record Lock
　　单条索引上加锁，record lock 永远锁的是索引，而非数据本身，如果innodb表中没有索引，那么会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。 Gap Lock
　　间隙锁，是在索引的间隙之间加上锁，这是为什么Repeatable Read隔离级别下能防止幻读的主要原因。
　　ps:幻读指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。
　　接用网上的例子 mysql> select * from product_copy; +----+--------+-------+-----+ | id | name   | price | num | +----+--------+-------+-----+ |  1 | 伊利   |    68 |   1 | |  2 | 蒙牛   |    88 |   1 | |  6 | tom    |  2788 |   3 | | 10 | 优衣库 |   488 |   4 | +----+--------+-------+-----+ 其中id为主键 num为普通索引 窗口A： mysql> select * from product_copy where num=3 for update; +----+------+-------+-----+ | id | name | price | num | +----+------+-------+-----+ |  6 | tom  |  2788 |   3 | +----+------+-------+-----+ 1 row in set  窗口B： mysql> insert into product_copy values(5,＂kris＂,1888,2); 这里会等待  直到窗口A commit才会显示下面结果 Query OK, 1 row affected  但是下面是不需要等待的 mysql> update product_copy set price=price+100 where num=1; Query OK, 2 rows affected Rows matched: 2  Changed: 2  Warnings: 0 mysql> insert into product_copy values(5,＂kris＂,1888,5); Query OK, 1 row affected
　　我们要关注的是索引字段num。通过上面的例子可以发现 num=2是被锁住的,而操作 num = 1 或者 5的都能正常使用。
　　即锁的作用是在1,3的间隙之间加上了锁。因此就防止了幻读的产生