MySQL学习笔记十六：锁机制
1.数据库锁就是为了保证数据库数据的一致性在一个共享资源被并发访问时使得数据访问顺序化的机制。MySQL数据库的锁机制比较独特，支持不同的存储引擎使用不同的锁机制。
2.MySQL使用了三种类型的锁机制，分别为：表级锁，行级锁，页级锁，它们的特性如下所示。
表级锁：实现逻辑较为简单，加锁速度快，开销小，不会发生死锁；但粒度最大，发生锁冲突的几率最大，并发度最小，适用于以查询为主，极少量更新的系统。
行级锁：加锁慢，开销大，会发生死锁；但粒度最小，锁冲突率小，并发度最高，使用于并发查询大，有大量按索引条件并发更新少量不同数据的系统。
页级锁：介于表级锁和行级锁之间。
3.MyISAM表锁
MyISAM存储引擎只支持表级锁，它的锁模式分为两种：表共享读锁和表独占写锁。MyISAM的读操作会阻塞其他用户的写操作，但不会阻塞其他用户的读操作。MyISAM的写操作会堵塞其他用户的读写操作。MyISAM的读写操作是串行的。
查询表锁的争用情况:
mysql> show status like ‘table_locks%‘; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 51 | | Table_locks_waited | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec) -----Table_locks_immediate 立即释放表锁数 -----Table_locks_waited 等待表锁数，该值如较高，说明表锁争用较严重
MyISAM表写操作阻塞示例：
| SESSION1 | SESSION2 |
|
获取stu表的写锁 mysql> lock table stu write; |
|
|
可以进行查询，更新，插入操作 mysql> select * from stu; mysql> update stu set gender=0 where sno=4010408; |
mysql> select * from stu; 查询被阻塞，等待锁释放 |
|
释放表写锁 mysql> unlock tables; |
等待 |
|
获得锁，查询返回 mysql> select * from stu; |
MyISAM表加锁
MyISAM表在执行select语句前会自动给涉及的所有表加上表读锁，执行update,delete,insert等更新语句前，会自动给所有涉及的表加写锁，一般不需要显式地给表加锁。显示地给表加锁，必须要同时取得所有涉及表的锁，并且不支持锁升级。在执行lock tables后，只能访问加锁的被加锁的表，不能访问未加锁的表。示例如下：
| SESSION1 | SESSION2 |
|
给表stu加上读锁 mysql> lock table stu read; |
|
|
可以查询stu表 mysql> select * from stu limit 2; |
|
|
不能查询stu以外的表 mysql> select * from student limit 2; |
可以查询或更新未锁定的表
mysql> select * from student limit 2; |
|
不能对stu表进行insert,update等操作 mysql> update stu set gender=1 where sno=4010408; |
更新操作会被阻塞 mysql> update stu set gender=1 where sno=4010408;
|
|
释放锁 mysql> unlock tables; |
等待 |
|
获得锁，更新完成 mysql> update stu set gender=1 where sno=4010408; |
使用lock tables锁定表时，如果查询用到该表的别名，则须要对别名也进行锁定，否则会报错，如下所示：
mysql> lock table stu read; Query OK, 0 rows affected (0.00 sec) mysql> select a.sno from stu a; ERROR 1100 (HY000): Table ‘a‘ was not locked with LOCK TABLES mysql> lock table stu as a read; Query OK, 0 rows affected (0.00 sec) mysql> select a.sno from stu a; +---------+ | sno | +---------+ | 4010404 | | 4010410 | | 4010405 | | 4010408 | | 4010409 | | 4010406 | | 4010407 | +---------+ 7 rows in set (0.00 sec)
MyISAM并发插入，总体而言，MyISAM的读写操作是串行的，在某些条件下，也是支持查询和插入操作的并发进行，MyISAMy引擎有一个系统变量concurrent_insert，可以用来控制并发插入行为，该值有6个内置值选择，如下所示：
| Value | Description |
|
NEVER (or |
禁止并发插入行为 |
|
AUTO (or |
在没有空洞时运行并发插入 |
|
ALWAYS |
不管有没有空洞允许表尾并发插入 |
并发插入示例：
| SESSION1 | SESSION2 |
|
锁定表t1 mysql> lock table t1 read local; |
|
|
不能进行更新，删除的等操作 mysql> update t1 set id=5 where id=1; |
可以插入记录，但更新会阻塞 mysql> insert into t1 values (10); mysql> update t1 set id=70 where id=60; |
|
不能查看到其他session插入的数据 mysql> select * from t1; |
|
|
释放锁 mysql> unlock tables; |
等待 |
|
可以查看其他SESSION更新的数据 mysql> select * from t1; |
更新完成 mysql> update t1 set id=70 where id=60; |
MyISAM锁调度
MyISAM的读锁和写锁是互斥的，读操作和写操作是串行的，一般情况下，写进程优先获得锁，即使是读进程请求先到锁等待队列，写进程后到。这样很容易造成大量的更新操作导致查询操作被永久阻塞，不过可以通过设置low_priority_updates变量值来调节MyISAM引擎的调度机制。
在mysql服务启动时，指定low_priority_updates参数让myisam引擎默认给予读请求更高的优先。
在session中，通过set low_priority_updates来降低当前的会话写请求的优先级。
4.InnoDB锁相关
InnoDB引擎对比MyISAM引擎，最大的不同之处在于，InnoDB支持事务和行级锁。
查看行级锁争用情况
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.00 sec) -------如果Innodb_row_lock_waits和Innodb_row_lock_time_avg值较高，则说明行级锁争用比较严重
InnoDB行锁分为两种类型：共享锁和排他锁。
共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
给记录集加共享锁，前提是当前没有线程对该结果集中的任何行使用排他锁，否则申请会阻塞，方式如下：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE mysql> select * from stu lock in share mode;
使用共享锁线程可对其锁定记录进行读取，其他线程同样也可对锁定记录进行读取操作，并且这两个线程读取的数据都属于同一个版本。
对于写入操作，使用共享锁的线程需要分情况讨论，当只有当前线程对指定记录使用共享锁时，线程是可对该记录进行写入操作（包括更新与删除），这是由于在写入操作前，线程向该记录申请了排他锁，然后才进行写入操作；当其他线程也对该记录使用共享锁时，则不可进行写入操作，系统会有报错提示。不对锁定记录使用共享锁的线程，当然是不可进行写入操作了，写入操作会阻塞。
使用共享锁线程可再次对锁定记录申请共享锁，系统并不报错，但是操作本身并没有太大意义。其他线程同样也可以对锁定记录申请共享锁。
使用共享锁进程可对其锁定记录申请排他锁；而其他进程是不可以对锁定记录申请排他锁，申请会阻塞。
示例如下：
| SESSION1 | SESSION2 |
|
对stu表所有记录加共享锁 mysql> set autocommit=0; mysql> select * from stu lock in share mode; |
|
|
可以对加锁记录进行读取操作，且也可以对该记录加共享锁 mysql> select * from stu lock in share mode; |
|
|
对锁定记录进行更新操作，等待锁 mysql> update stu set gender=0 where sno=4010406; |
|
|
也对锁定记录进行更新操作，发生死锁 mysql> update stu set gender=0 where sno=4010406; |
|
|
获得锁，更新完成 mysql> update stu set gender=0 where sno=4010406; |
排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
给记录集添加排他锁，前提是当前没有线程对该结果集中的任何行使用排他锁或共享锁，否则申请会阻塞。方式如下：
SELECT * FROM table_name WHERE ... FOR UPDATE mysql> select * from stu for update;
使用排他锁线程可以对其锁定记录进行读取，读取的内容为当前事物的最新版本；而对于不使用排他锁的线程，同样是可以进行读取操作，这种特性是一致性非锁定读。
使用排他锁线程可对其锁定记录进行写入操作；对于不使用排他锁的线程，对锁定记录的写操作是不允许的，请求会阻塞。
使用排他锁进程可对其锁定记录申请共享锁，但是申请共享锁之后，线程并不会释放原先的排他锁，因此该记录对外表现出排他锁的性质；其他线程是不可对已锁定记录申请共享锁，请求会阻塞。
使用排他锁进程可对其锁定记录申请排他锁（实际上并没有任何意义）；而其他进程是不可对锁定记录申请排他锁，申请会阻塞。
示例如下：
| SESSION1 | SESSION2 |
|
对表stu所有记录加排他锁 mysql> select * from stu for update; |
|
|
以查询该记录，但是不能对该记录加排他锁，会等待获得锁 mysql> select * from stu; mysql> select * from stu for update; |
|
|
可以对锁定记录进行更新，完成后锁释放 mysql> update stu set gender=1 where sno=4010406; mysql> commit; |
|
|
获取锁，返回查询记录并加上排他锁 mysql> select * from stu for update; |
InnoDB行锁实现方式：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
表stu的索引如下所示：
mysql> show index from stu\G *************************** 1. row *************************** Table: stu Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: sno Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: stu Non_unique: 1 Key_name: idx_name Seq_in_index: 1 Column_name: sname Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment:
不使用索引时，会使用表所，示例如下：
| SESSION1 | SESSION2 |
|
给gender=0的记录加上排他锁 mysql> select * from stu where gender=0 for update; |
|
|
给gender=1的记录加排他锁，被阻塞 mysql> select * from stu where gender=1 for update; |
使用索引检索时，使用行锁，示例如下：
| SESSION1 | SESSION2 |
|
给sname="祝小贤"的记录加上排他锁 mysql> select * from stu where sname=‘祝小贤‘ for update; |
|
|
给sname="程小锦"的记录加上排他锁 mysql> select * from stu where sname=‘程小锦‘ for update; |
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。如下所示：
| SESSION1 | SESSION2 |
|
给sname="祝小贤"和age=20的记录加上排他锁 mysql> select * from stu where sno=4010404 and age=20 for update; |
|
|
给sname="祝小贤"和age=21的记录加上排他锁，发生阻塞 mysql> select * from stu where sno=4010404 and age=21 for update; |
即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
间隙锁
|
给sno>4010409的记录加排他锁 mysql> select * from stu where sno>4010409 for update; |
|
|
向stu中插入一条 mysql> insert into stu values (4010411,‘zhangsan‘,‘A1114‘,30,1); |
|
|
释放next-key锁 mysql> rollback; |
|
|
更新成功 mysql> insert into stu values (4010411,‘zhangsan‘,‘A1114‘,30,1); |
使用insert into tbl_name select * from tbl_name或create table tbl_name as select * from tbl_name语句时，innodb会自动对源表加上共享锁。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
