MySQL锁机制

定义

定义：锁是计算机协调多个进程或线程并发访问某一资源的机制

锁的分类

锁的分类：

1.从对数据操作的类型（读/写）分

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

2.从对数据操作的粒度分

• 表锁
• 行锁

三锁

表锁

特点

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

案例分析(MyISAM引擎)

建表SQL

create table mylock (
id int not null primary key auto_increment,
name varchar(20)
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

加锁解锁命令

mysql> show open tables;
+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| mysql              | time_zone_transition_type                    |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name    |      0 |           0 |
| performance_schema | setup_timers                                 |      0 |           0 |
| performance_schema | events_waits_history_long                    |      0 |           0 |
| mysql              | time_zone_transition                         |      0 |           0 |
| performance_schema | mutex_instances                              |      0 |           0 |
| performance_schema | events_waits_summary_by_instance             |      0 |           0 |
| mysql              | tables_priv                                  |      0 |           0 |
| mysql              | procs_priv                                   |      0 |           0 |
| mysql              | func                                         |      0 |           0 |
| performance_schema | events_waits_history                         |      0 |           0 |
| mysql              | time_zone_name                               |      0 |           0 |
| mysql              | user                                         |      0 |           0 |
| deptemp            | mylock                                       |      0 |           0 |
| performance_schema | file_instances                               |      0 |           0 |
| performance_schema | cond_instances                               |      0 |           0 |
| mysql              | plugin                                       |      0 |           0 |
| mysql              | db                                           |      0 |           0 |
| mysql              | proxies_priv                                 |      0 |           0 |
| mysql              | time_zone                                    |      0 |           0 |
| performance_schema | rwlock_instances                             |      0 |           0 |
| performance_schema | events_waits_current                         |      0 |           0 |
| mysql              | event                                        |      0 |           0 |
| mysql              | columns_priv                                 |      0 |           0 |
| performance_schema | performance_timers                           |      0 |           0 |
| performance_schema | threads                                      |      0 |           0 |
| mysql              | host                                         |      0 |           0 |
| performance_schema | events_waits_summary_by_thread_by_event_name |      0 |           0 |
| performance_schema | file_summary_by_event_name                   |      0 |           0 |
| mysql              | time_zone_leap_second                        |      0 |           0 |
| performance_schema | file_summary_by_instance                     |      0 |           0 |
| performance_schema | setup_instruments                            |      0 |           0 |
| mysql              | servers                                      |      0 |           0 |
| performance_schema | setup_consumers                              |      0 |           0 |
+--------------------+----------------------------------------------+--------+-------------+
34 rows in set (0.00 sec)

-- 给mylock表加读锁，book表加写锁

mysql> lock table mylock read,book write;
Query OK, 0 rows affected (0.02 sec)

-- In_use字段为1表示已上锁

mysql> show open tables;
+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| mysql              | time_zone_transition_type                    |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name    |      0 |           0 |
| performance_schema | setup_timers                                 |      0 |           0 |
| performance_schema | events_waits_history_long                    |      0 |           0 |
| mysql              | time_zone_transition                         |      0 |           0 |
| performance_schema | mutex_instances                              |      0 |           0 |
| performance_schema | events_waits_summary_by_instance             |      0 |           0 |
| mysql              | tables_priv                                  |      0 |           0 |
| mysql              | procs_priv                                   |      0 |           0 |
| mysql              | func                                         |      0 |           0 |
| performance_schema | events_waits_history                         |      0 |           0 |
| mysql              | time_zone_name                               |      0 |           0 |
| mysql              | user                                         |      0 |           0 |
| deptemp            | mylock                                       |      1 |           0 |
| performance_schema | file_instances                               |      0 |           0 |
| performance_schema | cond_instances                               |      0 |           0 |
| mysql              | plugin                                       |      0 |           0 |
| mysql              | db                                           |      0 |           0 |
| mysql              | proxies_priv                                 |      0 |           0 |
| mysql              | time_zone                                    |      0 |           0 |
| performance_schema | rwlock_instances                             |      0 |           0 |
| performance_schema | events_waits_current                         |      0 |           0 |
| mysql              | event                                        |      0 |           0 |
| deptemp            | book                                         |      1 |           0 |
| performance_schema | performance_timers                           |      0 |           0 |
| performance_schema | threads                                      |      0 |           0 |
| mysql              | host                                         |      0 |           0 |
| performance_schema | events_waits_summary_by_thread_by_event_name |      0 |           0 |
| performance_schema | file_summary_by_event_name                   |      0 |           0 |
| mysql              | time_zone_leap_second                        |      0 |           0 |
| performance_schema | file_summary_by_instance                     |      0 |           0 |
| performance_schema | setup_instruments                            |      0 |           0 |
| mysql              | servers                                      |      0 |           0 |
| performance_schema | setup_consumers                              |      0 |           0 |
| mysql              | columns_priv                                 |      0 |           0 |
+--------------------+----------------------------------------------+--------+-------------+
35 rows in set (0.00 sec)

-- 解锁

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

mysql> show open tables;
+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| mysql              | time_zone_transition_type                    |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name    |      0 |           0 |
| performance_schema | setup_timers                                 |      0 |           0 |
| performance_schema | events_waits_history_long                    |      0 |           0 |
| mysql              | time_zone_transition                         |      0 |           0 |
| performance_schema | mutex_instances                              |      0 |           0 |
| performance_schema | events_waits_summary_by_instance             |      0 |           0 |
| mysql              | tables_priv                                  |      0 |           0 |
| mysql              | procs_priv                                   |      0 |           0 |
| mysql              | func                                         |      0 |           0 |
| performance_schema | events_waits_history                         |      0 |           0 |
| mysql              | time_zone_name                               |      0 |           0 |
| mysql              | user                                         |      0 |           0 |
| deptemp            | mylock                                       |      0 |           0 |
| performance_schema | file_instances                               |      0 |           0 |
| performance_schema | cond_instances                               |      0 |           0 |
| mysql              | plugin                                       |      0 |           0 |
| mysql              | db                                           |      0 |           0 |
| mysql              | proxies_priv                                 |      0 |           0 |
| mysql              | time_zone                                    |      0 |           0 |
| performance_schema | rwlock_instances                             |      0 |           0 |
| performance_schema | events_waits_current                         |      0 |           0 |
| mysql              | event                                        |      0 |           0 |
| deptemp            | book                                         |      0 |           0 |
| performance_schema | performance_timers                           |      0 |           0 |
| performance_schema | threads                                      |      0 |           0 |
| mysql              | host                                         |      0 |           0 |
| performance_schema | events_waits_summary_by_thread_by_event_name |      0 |           0 |
| performance_schema | file_summary_by_event_name                   |      0 |           0 |
| mysql              | time_zone_leap_second                        |      0 |           0 |
| performance_schema | file_summary_by_instance                     |      0 |           0 |
| performance_schema | setup_instruments                            |      0 |           0 |
| mysql              | servers                                      |      0 |           0 |
| performance_schema | setup_consumers                              |      0 |           0 |
| mysql              | columns_priv                                 |      0 |           0 |
+--------------------+----------------------------------------------+--------+-------------+
35 rows in set (0.00 sec)

加读锁

读锁是共享锁，其他session也能查看(左session1，右session2)

-- session1  可以读锁住的表，不能写，不能查看其他表
mysql> update mylock set name = 'a2' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

-- session2  可以读session1锁住的表，不能写（阻塞，只要锁住的表解锁，立马执行写操作），也能查看其他表
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|     19 |    1 |
|      5 |    2 |
|     14 |    4 |
|     20 |    4 |
|      1 |    5 |
|      6 |    5 |
|      3 |    6 |
|     16 |    7 |
|     18 |    7 |
|      4 |    8 |
|     13 |    9 |
|     10 |   10 |
|     15 |   12 |
|      9 |   13 |
|     11 |   13 |
|     12 |   14 |
|     17 |   17 |
|      8 |   18 |
|      7 |   19 |
|      2 |   20 |
+--------+------+
20 rows in set (0.00 sec)

表还没解锁，session2：阻塞

表一解锁后session2：立马执行完成

加写锁

-- session1 可以读也能写锁住的表，不能读其他表
mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a3   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

mysql> update mylock set name = 'a4' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

session2:

可以读写其他表，不能读、写session1锁住的表，阻塞，需要等待锁被释放

案例结论

总结：MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

• 表共享读锁（Table Read Lock）
• 表独占写锁（Table Write Lock）

结论：

结合上表，所以对MyISAM表进行操作，会有以下情况：

1.对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。

2.对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

行锁

特点

特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

InnoDB与MyISAM的最大不同有两点：

• 一是支持事务
• 二是采用了行级锁

案例分析

需要关闭mysql自动提交

SET autocommit=0;

建表SQL

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);

set autocommit = 0;

session1:

mysql> update test_innodb_lock set b = 4001 where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4001 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

session2:

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

session1 commit后session2commit后session2:

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4001 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

测试行锁

1.操作同一行数据

-- session1
mysql> update test_innodb_lock set b = 4002 where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4002 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

-- session2 阻塞
mysql> update test_innodb_lock set b = 4003 where a =4;

session1commit后session2：立即执行完成

Query OK, 1 row affected (2.94 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时session1查询结果是4002

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4002 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

而session2查询结果是4003

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4003 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

session2 commit后session1再commit后，session1更新为4003

2.操作不同行数据

-- session1
mysql> update test_innodb_lock set b = 4009 where a = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- session 2
mysql> update test_innodb_lock set b = 9009 where a =9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

两边commit后都能看见修改的值

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4009 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9009 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

无索引行锁升级表锁

-- session1  
mysql> update test_innodb_lock set a = 41 where b = 4009;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- session2  由于b字段是varcahr类型，查询语句b=4009是int类型，mysql底层将它进行了转换，索引列上进行了类型转换从而导致索引失效，从而导致行锁升级表锁，所以session2的更新语句被阻塞
mysql> update test_innodb_lock set b = 9008 where a =9;

session1commit后session2：立即执行完成

Query OK, 1 row affected (3.82 sec)
Rows matched: 1  Changed: 1  Warnings: 0

间隙锁危害

定义：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

-- session1 锁定2,3,4,5行，即使2不存在
mysql> update test_innodb_lock set b = 'test' where a > 1 and a < 6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

-- session2  被阻塞
mysql> INSERT INTO test_innodb_lock VALUES(2, '2000');

session1commit后session2插入成功

Query OK, 1 row affected (6.68 sec)

案例结论

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量高的时候，InnoDB的整体性能和MyISAM相比就会有明显的优势了。

其次，InnoDB的行级锁一定要使用恰当，要不就适得其反，会让InnoDB的整体性能表现比MyISAM差。

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

Changed: 3 Warnings: 0

```sql
-- session2  被阻塞
mysql> INSERT INTO test_innodb_lock VALUES(2, '2000');

session1commit后session2插入成功

Query OK, 1 row affected (6.68 sec)

案例结论

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量高的时候，InnoDB的整体性能和MyISAM相比就会有明显的优势了。

其次，InnoDB的行级锁一定要使用恰当，要不就适得其反，会让InnoDB的整体性能表现比MyISAM差。

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。