Mysql学习之锁机制（Lock）

锁概念

       锁是计算机协调多个进程或线程并行访问同一资源的机制。对于数据库，如何保证数据资源并行访问的一致性、有效性、原子性是非常重要的。Mysql 数据库的锁相对比较简单，最著名的特性是不同的存储引擎支持不同的锁，比如MyIsam只支持表级锁，Innodb默认使用行级锁，但也支持表级锁，BDB支持页级索与表级锁。Mysql都是在操作之前上锁，这种锁叫悲观锁。

 

锁按级别分类

• 表级锁：开销小，加锁快，不会产生死锁，锁定粒度大，发生锁冲突的概率高，并发度最低；
• 页级索：开销中，加锁中，会产生死锁，锁定粒度一般，发生锁冲突的概率一般，并发度一般；
• 行级锁：开销大，加锁慢，会产生死锁，锁定粒度小，发生锁冲突概率低，并发度高；

    很难笼统的说哪种锁比较好，只能就具体应用的特点选择合适的锁，表级锁适合以查询为主，只进行少量维护的的应用；行级锁适合查询且有大量按索引并发更新数据的应用。

 

阻塞与死锁

• 阻塞：由于不同锁之间的兼容关系，有些时候一个事务中的锁等待另一个事务中的锁释放，就会造成阻塞；
• 死锁：死锁是指多个事务在执行的过程中，相互占用对方等待的资源而产生的异常；

 

表级锁

* 查询表级锁信息

# show status like 'table%'; // 如果Table_locks_waited的值比较高说明存在着较严重的表级锁争用情况

* 显示的使用表级锁

# lock tables table_name_1 read,table_name_2 read // 加共享读锁
# lock tables table_name_1 read local,table_name_2 read local // 加共享读锁，其它会话可以在表末尾写入数据
# lock tables table_name_1 write,table_name_2 write // 加独占写锁
# unlock tables; // 释放锁

* 表级锁调度
一个进程请求表的读锁，另一个进程请求表的写锁，那么Mysql是优先写锁，如果写跟更新太频繁，可能会造成永远阻塞，可以使用下面的方式更改优先级：

• 通过指定启动参数low-priority-updates，使引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该会话发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

* Mysql 表级锁又可分为共享读锁与独占写锁：

1. 共享读锁，见案例：

2 . 独占写锁，见案例

 

Innodb行锁

* InonoDB行锁争用情况

# show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

* Innodb显示加锁

# select ... from ... where ... lock in share mode; // 查询数据加共享锁
# select ... from ... where ... for update; // 查询数据时加排它锁
# insert、update、delete 语句自动加排它锁

* Innodb行锁模式

• 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

注意：排他锁锁住一行数据后，不是其他事务就不能读取和修改该行数据，是指其他事务不能再在其上加其他的锁，也就不能更新，不能被select ... lock in share mode，不能被select ... for update等操作，但是可以使用普通查询。

* Innodb行锁实现方式

1. 在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
2. 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。 
3. 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。