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;
Query OK, 0 rows affected (0.03 sec)

  

可以进行查询，更新，插入操作

mysql> select * from stu;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010406 | 钟小喜 | A1014 | 24 | 1 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 1 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
7 rows in set (0.03 sec)

mysql> update stu set gender=0 where sno=4010408;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from stu;

查询被阻塞，等待锁释放

释放表写锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

 等待
 

获得锁，查询返回

mysql> select * from stu;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010406 | 钟小喜 | A1014 | 24 | 1 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 0 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
7 rows in set (4 min 27.54 sec)

MyISAM表加锁

MyISAM表在执行select语句前会自动给涉及的所有表加上表读锁，执行update,delete,insert等更新语句前，会自动给所有涉及的表加写锁，一般不需要显式地给表加锁。显示地给表加锁，必须要同时取得所有涉及表的锁，并且不支持锁升级。在执行lock tables后，只能访问加锁的被加锁的表，不能访问未加锁的表。示例如下：

SESSION1 SESSION2

给表stu加上读锁

mysql> lock table stu read;
Query OK, 0 rows affected (0.49 sec)

  

可以查询stu表

mysql> select * from stu limit 2;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
+---------+--------+-------+------+--------+
2 rows in set (0.15 sec)

  

 不能查询stu以外的表

mysql> select * from student limit 2;
ERROR 1100 (HY000): Table 'student' was not locked with LOCK TABLES

 可以查询或更新未锁定的表 

mysql> select * from student limit 2;
+---------+-----------+--------+------+---------+
| sno | sname | sclass | sage | sgender |
+---------+-----------+--------+------+---------+
| 4010404 | zhumuxian | A1114 | 20 | 男 |
+---------+-----------+--------+------+---------+
1 row in set (0.29 sec)

不能对stu表进行insert,update等操作

mysql> update stu set gender=1 where sno=4010408;
ERROR 1099 (HY000): Table 'stu' was locked with a READ lock and can't be updated

 更新操作会被阻塞

mysql> update stu set gender=1 where sno=4010408;

 释放锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

  等待
 

 获得锁，更新完成

mysql> update stu set gender=1 where sno=4010408;
Query OK, 1 row affected (5 min 23.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0

使用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
0)

 禁止并发插入行为

AUTO (or
1)

 在没有空洞时运行并发插入

ALWAYS
(or 2)

 不管有没有空洞允许表尾并发插入

并发插入示例：

SESSION1 SESSION2

锁定表t1

mysql> lock table t1 read local;
Query OK, 0 rows affected (0.00 sec)

  

 不能进行更新，删除的等操作

mysql> update t1 set id=5 where id=1;
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

 可以插入记录，但更新会阻塞

mysql> insert into t1 values (10);
Query OK, 1 row affected (0.12 sec)

mysql> update t1 set id=70 where id=60;

 不能查看到其他session插入的数据

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 4 |
| 30 |
| 60 |
+----+
4 rows in set (0.00 sec)

  

释放锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

  等待

 可以查看其他SESSION更新的数据

mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 4 |
| 10 |
| 30 |
| 70 |
+----+
5 rows in set (0.00 sec)

更新完成

mysql> update t1 set id=70 where id=60;
Query OK, 1 row affected (7 min 44.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu lock in share mode;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010406 | 钟小喜 | A1014 | 24 | 1 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 0 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
7 rows in set (0.00 sec)

  
 

 可以对加锁记录进行读取操作，且也可以对该记录加共享锁

mysql> select * from stu lock in share mode;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010406 | 钟小喜 | A1014 | 24 | 1 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 0 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
7 rows in set (0.00 sec)

 对锁定记录进行更新操作，等待锁

mysql> update stu set gender=0 where sno=4010406;

  
 

 也对锁定记录进行更新操作，发生死锁

mysql> update stu set gender=0 where sno=4010406;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 获得锁，更新完成

mysql> update stu set gender=0 where sno=4010406;
Query OK, 1 row affected (10.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

  

排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

给记录集添加排他锁，前提是当前没有线程对该结果集中的任何行使用排他锁或共享锁，否则申请会阻塞。方式如下：

SELECT * FROM table_name WHERE ... FOR UPDATE
mysql> select * from stu for update;

使用排他锁线程可以对其锁定记录进行读取，读取的内容为当前事物的最新版本；而对于不使用排他锁的线程，同样是可以进行读取操作，这种特性是一致性非锁定读。

使用排他锁线程可对其锁定记录进行写入操作；对于不使用排他锁的线程，对锁定记录的写操作是不允许的，请求会阻塞。

使用排他锁进程可对其锁定记录申请共享锁，但是申请共享锁之后，线程并不会释放原先的排他锁，因此该记录对外表现出排他锁的性质；其他线程是不可对已锁定记录申请共享锁，请求会阻塞。

使用排他锁进程可对其锁定记录申请排他锁（实际上并没有任何意义）；而其他进程是不可对锁定记录申请排他锁，申请会阻塞。

示例如下：

SESSION1 SESSION2

对表stu所有记录加排他锁

mysql> select * from stu for update;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010406 | 钟小喜 | A1014 | 24 | 0 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 0 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
7 rows in set (0.00 sec)

  
 

 以查询该记录，但是不能对该记录加排他锁，会等待获得锁

mysql> select * from stu;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010406 | 钟小喜 | A1014 | 24 | 1 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 0 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from stu for update;

 可以对锁定记录进行更新，完成后锁释放

mysql> update stu set gender=1 where sno=4010406;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

  
 

 获取锁，返回查询记录并加上排他锁

mysql> select * from stu for update;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010406 | 钟小喜 | A1014 | 24 | 1 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 0 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
7 rows in set (7.65 sec)

InnoDB行锁实现方式：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

表stu的索引如下所示：

mysql> show index from stuG
*************************** 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;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010405 | 肖小杰 | A1013 | 22 | 0 |
| 4010407 | 钟小惠 | A1015 | 26 | 0 |
| 4010408 | 肖小杰 | A1114 | 21 | 0 |
| 4010409 | 钟小兆 | A1114 | 22 | 0 |
+---------+--------+-------+------+--------+

  
 

 给gender=1的记录加排他锁，被阻塞

mysql> select * from stu where gender=1 for update;

使用索引检索时，使用行锁，示例如下：

SESSION1 SESSION2

给sname="祝小贤"的记录加上排他锁

mysql> select * from stu where sname='祝小贤' for update;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
+---------+--------+-------+------+--------+

  
 

 给sname="程小锦"的记录加上排他锁

mysql> select * from stu where sname='程小锦' for update;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
1 row in set (0.04 sec)

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

SESSION1 SESSION2

给sname="祝小贤"和age=20的记录加上排他锁

mysql> select * from stu where sno=4010404 and age=20 for update;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010404 | 祝小贤 | A1012 | 20 | 1 |
+---------+--------+-------+------+--------+

  
 

给sname="祝小贤"和age=21的记录加上排他锁，发生阻塞

mysql> select * from stu where sno=4010404 and age=21 for update;

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：
Select * from  emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。
如下所示：
   

给sno>4010409的记录加排他锁

mysql> select * from stu where sno>4010409 for update;
+---------+--------+-------+------+--------+
| sno | sname | class | age | gender |
+---------+--------+-------+------+--------+
| 4010410 | 程小锦 | A1114 | 22 | 1 |
+---------+--------+-------+------+--------+
1 row in set (0.03 sec)

  
 

向stu中插入一条 

mysql> insert into stu values (4010411,'zhangsan','A1114',30,1);

释放next-key锁

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

  
 

更新成功

mysql> insert into stu values (4010411,'zhangsan','A1114',30,1);
Query OK, 1 row affected (27.06 sec)

使用insert into tbl_name select * from tbl_name或create table tbl_name as select * from tbl_name语句时，innodb会自动对源表加上共享锁。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zmxmumu/p/4459745.html