深度浅出mysql_锁问题

二十 锁问题


20.1 MySQL 锁概述


表级锁（table-level-lock）


特点：开销小、加锁块；不会出现死锁；锁定粒度大、发生锁冲突的概率最高，并发度最低；


更适合以查询为主，只有少量按索引更新数据的应用，如web应用


行级锁（row-level-lock）


特点： 开销大，加锁慢；会出现死锁；锁定粒度最小，发送锁冲突的概率最低，并发也最高


适合 有大量按照索引条件 并发更新少量不同数据的，同时又有并发查询的应用， 如一些在线事务处理（oltp）系统；


页级锁（page-level-lock）


特点：开销和加锁时间界于行锁和表锁之间；会出现死锁；锁定粒度界于表所和行锁之间，并发度一般；


20.2 MyISAM 表锁


20.2.1 查询表级锁争用情况


可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺。如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况。


范例


mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec))


20.2.2 MySQL 表级锁的锁模式


1、 表共享读锁 
不会堵塞其他用户对同一标的读操作，但是会堵塞写操作


2、 表独占写锁
会堵塞读写操作


范例


根据如表 20-2 所示的例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。


20.2.3 如何加表锁


MyISAM在执行查询语句（SELECT）会自动给所有涉及的表加读锁，
在执行更新操作（INSERT update delete等）前，会自动给涉及的表加写锁。这个过程并不需要用户干预。因此用户一般不需要直接用LOCK TABLE 命名给MyISAM表显式的进行加锁。


需要实现一个时间对多个表的读取一致性时，需要显式的加锁；


例：同时读取 order 和order_detail 表时
>lock tables order read local,order_detail read local;
>select sum(total) from order;
>select sum(subtoal) from order_detail;
>unlock tables;


20.2.4 并发插入（Concurrent Inserts）


在一定条件下，MyISAM支持查询和插入的并发执行


MyISAM 存储引擎有个系统变量 concurrent_insert  专门用来控制并发插入行为
其值可以分别为 0 1 2
0：不允许并发插入
1 : 如果MyISAM中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程在表尾插入记录。这是他的默认值；
2： 无论MyISAM表有么有空洞 都允许从表位并发插入数据；（可以设置为2，配合定期执行optimize table 来整理空间碎片）


20.2.5 MyISAM 的锁调度


读锁和写锁是互斥的，读写操作是串行的；
写锁优先于读锁，即使当读锁先到锁等待队列，写锁后到等待队列，写锁也会插入到读锁等待队列之前。因为MySQL认为一般写请求比读请求更重要；这也是因为MyISAM不适合有大量更新操作和查询操作的原因；



设置修改默认参数：
1, 通过指定启动参数，low_priority_update参数，使MyISAM引擎默认给予读请求优先的权利
2. 通过执行命令 SET LOW_PRIORITY_UPDATES=1,使该链接发出的更新请求优先级降低
3. 通过指定 INSERT 、 UPDATE、DELETE语句的LOW_PRIORTY 属性，降低该语句的优先级；


另外mysql 也提供了一些折中的办法来调节读写冲突，通过设置系统参数 max_write_lock_count 参数设置一个合适的值，当一个表的读锁达到这个值后，mysql就会暂时降低写请求的优先级。
长时间的读锁也会饿死 写锁，所以避免长时间的sql查询操作，不要总想用一条sql解决问题，因为这些看似巧妙的sql 往往比较复杂，执行时间较长。


InnoDB锁问题


20.3.1 背景知识


1 事务及其ACID属性


原子性：
事务操作是一个原子操作，其对数据的修改，要么全部执行，要么全不执行


一致性:
在事务开始和完成时，数据都必须保持一致的状态，这意味着，所有相关的数据规则都必须应用于事务的修改，以保证事务的完整性。



持久性：
事务完成后 对数据的修改时永久性的


隔离性：
数据库系统提供一定的隔离机制，保证事务在不受并发操作影响的“独立”环境执行


2 并发事务处理带来的问题


1更新丢失（lost update）：
当连个或者多个事务选择了统一行，然后基于最初的选择值。然后基于最初选择的行更新该行时候，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题，最后一个更新覆盖了其他更新；


2 脏读（dirty read）：
一个事务正在对一条记录做修改，在这个事务完成提交前，这条记录的数据就处于不一致的状态；这时


3。不可重复读（non-repeateable-reads）：
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现这个数据已经修改过了，删除了；这用现象就叫做不可重复读；



4、幻读（phantom reads）：
一个事务按相同查询条件读取以前检索过得数据，却发现其他事务插入了满足其条件查询的语句。 这种现象较幻读；


3 事务隔离级别


20.3.2 获取 InnoDB 行锁争用情况


通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况


mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.01 sec)
如果发现 innodb_row_lock_waits 和InnoDB_row_lock_time_avg比较高，说明锁争用比较严重


如果锁争用较高 ，分析方法


1 通过查询 information_schema 数据库中的表 了解锁的等待情况；
> select * from innnodb_locks \G;


2 通过设置 InnoDB monitor 观察锁冲突情况
>create table innodb_monitor(a INT) engine = Innodb;
然后就可以通过下面的语句进行查看：
>select engine innodb status\G;
监视器可以通过下面语句停止：
>drop table innodb_monitor;


设置监视器后，在SHOW INNODB STATUS 的显示中，会有详细的当前锁的等待信息，包括表名，锁类型，锁定记录的情况等；便于进一步分析问题；


20.3.3 InnoDB 的行锁模式及加锁方法


行锁的两种类型


共享锁（S）：
允许一个事务去读一行，阻止其他事物获得相同数据集的排它锁


排它锁（X）：
允许获得排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁


为了允许表锁和行锁共存，实现多粒度锁机制。
mysql 还支持两种内部使用的意向锁（Intention Locks）这两种意向锁都是表锁


意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给数据行加共享锁之前必须获得该表的IS锁


意向排它锁（IX）：事务打算给数据行加行排它锁的时候，必须获得该表的IX锁；


加锁方法：


对于 insert  update delete 语句，InnoDB会自动给涉及的数据加上排它锁，对于SELECT语句 InnoDB不会加任何锁；


显式的给语句加锁：
语法：
SELECT * FROM table_name WHERE ....LOCK IN SHARE MODE;（共享锁）
SELECT * FROM table_name WHERE .....FOR UPDATE ;（排它锁）


共享锁 主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对该条记录进行update 或者delete操作。但是如果当前事务，也需要对该条记录进行更新操作的应用，应用应该使用SELECT .... FOR UPDATE 方式获得排他锁。



20.3.4 InnoDB 行锁实现方式


InnoDB行锁是通过给索引上索引项加锁实现的。如果没有索引，InnoDB将通过隐藏的聚集索引来对记录加锁。


InnoDB的行锁的三种方式：


1. record lock 对索引项加锁
2. gap lock: 对索引项之间 的“间隙”、第一记录前的间隙、最后一条记录后的间隙 加锁
3. next-key lock： 前两种的组合，对记录及其前面的间隙加锁


1)不通过索引查询数据时， InnoDB会锁定表中所有记录


2） 由于 MySQL的行锁是针对索引的加的锁，不是针对记录加的锁，所以虽然是访问的不同行的记录，但是如果是使用了相同行的索引键，是会出现所冲突的。


3）当表中有多个索引的时候，不同事物可以使用不同的索引锁定不同的行，无论是主键索引、唯一索引、还是普通索引。


4）即便在条件中使用了索引，但是否通过索引来检索数据是mysql通过判断不同的执行计划来决定的。 所以在分析锁冲突的时候 别忘了检查sql的执行计划。


20.3.5 间隙锁next_key锁


当我们用范围条件而不是想到能条件检索数据时，并请求共享锁或者排它锁的时候，InnoDB会给已有数据记录的索引项加锁；对于键值在条件范围内，但是并不存在的记录（叫做 间隙 GAP），InnoDB也会对这个间隙加锁。这种锁机制就是所谓的next-key


20.3.6 恢复和复制的需要，对 InnoDB 锁机制的影响


20.3.7 InnoDB 在不同隔离级别下的一致性读及锁的差异


20.3.8 什么时候使用表锁


在个别特殊事务中，可以考虑使用表锁
但是这种事务不能太多，负责就应该考虑使用MyISAM表了


1 第一种情况：事务需要更大部分或者全部数据，表又比较大，如果使用行锁，不仅这个事务执行效率较低，而且可能造成其他事务长时间的所等待和锁冲突。这种情况下可以使用表锁来提高事务的执行效率


2 第二种情况： 事务设计多个表，比较复杂，很可能引起死锁，造成造成大量事务回滚，这种情况也可以考虑使用表锁，从而避免死锁，减少数据库事务回滚带来的开销。


InnoDB下使用表锁注意


1 . 使用lock tables 虽然可以给InnoDB加表锁，但是表锁不是由InnoDB存储引擎层管理的。而是由他的上一层 MySQL server 管理的，仅当autocommit=0、 InnoDB_table_lock=1时，InnoDB才能知道mysql加的表锁，mysql-servier 也才能知道InnoDB加的行锁，这种情况下InnoDB才会自动识别设计表锁的死锁；否则 InnoDB将无法自动检测并处理这种死锁


2. 在用LOCK_TABLES对InnoDB表加锁时要注意， 要将autocommit 设为0 ，否则MySQL不会给表加锁；事务结束前，不要使用unlock tables释放表锁，因为unlock tables 会隐含的提交事务：
正确方式：
>set autocommit=0;
>lock tables t1 write. t2 read,....;
>[do somting with tables ....]
>commit;
>unlock tables;


20.3.9 关于死锁


发生死锁后，一般InnoDB都能自动检测到，并使一个事务释放锁，并回退，另一个事务得锁，继续完成事务。 但是在设计表锁 或者外部锁，InnoDB并不能自动检测到死锁，这时可以通过设置锁等待超时参数 InnoDB_lock_wait_time来解决


通常来说，死锁应该都是应用设计的问题。通过调整业务流程、数据对象设计、事务大小，以及访问数据库的sql语句，绝大部分死锁都能可以避免。


1、在应用中，如果不同的应用程序会并发的存取多个表，应尽量约定以相同的顺序访问表，这样大大降低了死锁发生的机会。


2. 在程序以批量的方式读取数据的时候，如果事先读数据排序，保证每个线程以固定的顺序来处理记录，也可以大大降低出现死锁的可能。


3、在事务中，如果要修改记录，应该直接申请足够级别的锁，即排它锁，而不应该先申请共享锁，更新时再申请排他锁


4参照阅读隔离级别，在 READTABLE-READ 隔离级别下，如果两个线程同时对相同数据用select ....for update 排它锁，在没有符合该条件记录的情况下，两个线程都会加锁成功。程序发现记录存在，就试图插入一条新纪录，如果两个线程都这么做，就会发生死锁。 在这种情况下，将隔离级别改成read-commit 就能解决问题


5 、 当隔离级别为read-commited，当两个线程都先执行 SELECT ... FOR UPDATE 判断是否有符合条件的记录，如果没有，就插入记录。此时 只有一个线程能插入成功，另一个线程会出现锁等待，当第一个线程提交后，第二个线程会因主键重复出错，但是虽然这个线程出错了，但是他获得了一个排它锁，这时如果第三个进程再来申请排它锁，也会出现死锁