Mysql 锁

一.锁的分类

• 锁的级别分类
• 读锁(共享锁)： 针对同一份数据，多个读操作之间互不影响
• 写锁(排它锁)： 当前写操作未完成前，阻塞其它写操作和读操作
• 锁操作粒度分类
• 表锁： 分为表共享锁和表独占锁，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
• 行锁： 分为共享锁和排它锁，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
• 页锁： 页锁的粒度介于行锁和表锁之间(一次锁定相邻的一组数据)，开销介于表锁和行锁之间；加锁速度介于行锁和表锁之间；会出现死锁，并发度一般。
• 加锁方式分类
• 自动锁：
• 显示锁：
• 使用方式分类
• 悲观锁： "先取锁再分配"的保守策略，为数据处理提供了安全保障，但加锁会产生额外的开销，增加了死锁的机会
• 乐观锁： 相对悲观锁，乐观锁以记录数据版本的形式实现乐观锁，为数据增加版本标识，数据更新的同时更新版本号

---

二.MyISAM存储引擎
1.建表

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

2.插入数据

INSERT INTO myisam_lock VALUES (1, 'a');
INSERT INTO myisam_lock VALUES (2, 'b');
INSERT INTO myisam_lock VALUES (3, 'c');
INSERT INTO myisam_lock VALUES (4, 'd');

3.锁命令

#手动加锁
lock table 表名称 read(write),表名称2 read(write)，其他;

#表解锁
unlock tables;

#查看表上加的锁
show open tables;

4.表加锁测试

• 读锁：
• 数据库连接1加读锁：分别测试连接1读取和修改数据

#连接1加读锁
lock table myisam_lock read ;

#1)读取数据
mysql> select * from myisam_lock ;
+------+--------+
|  id  |  name  |
+------+--------+
|    1 | a      |
|    2 | b      |
|    3 | c      |
|    4 | d      |
+------+--------+

#2)插入数据
mysql> insert into myisam_lock values (5,'e');
ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated

• 数据库连接2不加锁：在数据库连接1加读锁的基础上，分别测试连接2读取和修改数据。数据库连接1再释放读锁，分别测试连接2读取和修改数据

#1)数据库连接2直接读取数据
mysql> select * from myisam_lock ;
+------+--------+
|  id  |  name  |
+------+--------+
|    1 | a      |
|    2 | b      |
|    3 | c      |
|    4 | d      |
+------+--------+

#2)数据库连接2，直接插入数据，线程一直被阻塞
mysql> insert into myisam_lock values (5,'e');

#3)数据库连接1释放读锁，数据库连接2的插入操作成功
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into myisam_lock values (5,'e');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

• 写锁：
• 数据库连接1加写锁：分别测试连接1读取和修改数据

#连接1加写锁
lock table myisam_lock write ;

#1)读取数据
mysql> select * from myisam_lock;
+------+--------+
|  id  |  name  |
+------+--------+
|    1 | a      |
|    2 | b      |
|    3 | c      |
|    4 | d      |
|    5 | e      |
+------+--------+
5 rows in set (0.00 sec)

#2)插入数据
mysql> insert into myisam_lock values (6,'f');
Query OK, 1 row affected (0.01 sec)

• 数据库连接2不加锁：在数据库连接1加写锁的基础上，分别测试连接2读取和修改数据，再将数据库连接1的写锁释放，分别测试连接2读取和修改数据

#1)数据库连接2直接读取数据，会被一直阻塞
mysql> select * from myisam_lock;

#2)数据库连接2，直接插入数据，会被一直被阻塞
mysql> insert into myisam_lock values (5,'e');

#3)数据库连接1释放写锁，数据库连接2查询数据成功
mysql> select * from myisam_lock ;
+------+--------+
|  id  |  name  |
+------+--------+
|    1 | a      |
|    2 | b      |
|    3 | c      |
|    4 | d      |
|    5 | e      |
|    6 | f      |
+------+--------+
6 rows in set (4 min 57.86 sec)

5.表锁结论
读锁：当前加锁线程能读、写操作报错，其它线程能读、写操作被阻塞(当读锁被释放，可写成功)
写锁：当前加锁线程能读、能写，其它线程的读写操作会被阻塞(当写锁被释放，可读写成功)
myisam适合做读为主的表，不适合做写为锁的表，因为写操作会加写锁，影响其它线程的读操作
6.表锁分析

• 表锁参数：

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 25365 |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 3     |
| Table_open_cache_misses    | 3     |
| Table_open_cache_overflows | 3     |
+----------------------------+-------+
5 rows in set (0.00 sec)

Table_locks_immediate：产生表级锁定的次数，表示立即获取锁的查询次数，每次立即获取锁加1
Table_locks_waited：出现表级抢锁的次数，表示不能立即获取锁需要等待，每等待一次锁加1，此值高说明表级锁争抢严重

---

三.Innodb存储引擎
1.事务ACID

• A：原子性，事务是一个原子操作单元，其对数据的修改要么全执行成功，要么全执行失败
• C：一致性，在事务开始和完成时，都必须保证数据的一致状态，所有数据的相关规则都必须应用于事务的修改；事务结束时，所有的内部数据结构，(如B+树索引或双向链表)也都必须正确
• I：隔离性，数据库提供一定的隔离环境，保证事务在不受外部并发操作影响的’独立 '环境下执行，事务处理过程中的中间状态不可见
• D：持久性，事务处理完成后，它对数据的修改是永久的

2.事务带来的问题

• 脏读：
  事务A读到了事务B未提交的数据，如果事务B回滚或又再次修改数据，A事务读取的数据无效，这不符合数据的一致性(读到事务提交中的数据)
• 不可重复读：
  事务A先读到的数据是a，等事务B提交了事务，再次读到的数据是b，前后读到的数据不一致(读到事务提交前后的数据)
• 幻读
  事务A先读取数据，没发现符合条件的数据，事务B提交了事务，事务A又读到了数据，出现了幻读(如读到的数据行数变多)

3.事务的隔离级别

• 脏读、不可重复读、幻读都是数据库的数据一致性问题，通过数据库提供的事务隔离机制来解决
• 查看数据库的事务隔离级别：show VARIABLES like 'tx_isolation'

隔离级别	数据一致性	脏读	不可重复读	幻读
读未提交	最低级别的数据读取	会	会	会
读已提交	语句级	不会	会	会
可重复读	事务级	不会	不会	会
可序列化	最高级别事务级别	不会	不会	不会

4.Innodb行锁的实现方式

• Innodb上的行锁是通过表上的索引实现的，所以：只有通过索引检索数据，Innodb才会使用行锁，否则会使用表锁

5.建表

CREATE TABLE test_innodb_lock ( id INT ( 11 ), name VARCHAR ( 16 ) ) ENGINE = INNODB;

6.插入数据

INSERT INTO test_innodb_lock VALUES (1, 'a');
INSERT INTO test_innodb_lock VALUES (2, 'b');
INSERT INTO test_innodb_lock VALUES (3, 'c');
INSERT INTO test_innodb_lock VALUES (4, 'd');

7.加锁测试

• 在数据库连接1更新但不提交事务，测试数据库连接2读取、修改数据

#1)数据库连接1设置手动提交事务
mysql> mysql> set autocommit = 0 ;
Query OK, 0 rows affected (0.00 sec);

#2)数据库连接1更新操作
mysql> update test_innodb_lock set name = 'a1' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#3)数据库连接1，读取数据
mysql> select * from test_innodb_lock;
+------+------+
| id   | name |
+------+------+
|    1 | a1   |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

#4)数据库连接2，读取数据
mysql> select * from test_innodb_lock;
+------+------+
| id   | name |
+------+------+
|    1 | a1   |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

#5)数据库连接2，修改数据，数据连接1一直不提交事务,会导致连接2的修改报事务超时的error
mysql> update test_innodb_lock set name = 'b2' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

#6)数据库连接2，修改数据后，数据连接1在超时时间，内提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> update test_innodb_lock set name = 'b2' where id = 2;
(在超时时间内，数据库连接1执行事务提交命令)
Query OK, 1 row affected (5.15 sec)
Rows matched: 1  Changed: 1  Warnings: 0

8.Select 加锁

• 读锁：共享锁（share lock）
  读锁，其它用户可以并发的读取数据，但任何事务都不能对数据进行修改，即加了共享锁的数据，其它线程可以再对它加共享锁，但任何线程都不能对加了共享锁但未释放共享锁的数据加排它锁
• 用法：select ... lock in share mode ;
  在查询语句后使用 lock in share mode ，mysql会对查询的每行结果加共享锁，当没有其它连接或线程对查询结果集中的任何一行数据加排他锁，会加共享锁成功，否则会一直阻塞。其它线程可以读取加了共享锁的数据，读到的数据为加锁的数据。
• 写锁：排它锁（exclusive lock）
  写锁，事务对数据加上写锁后，其它任何事务都不能对该数据加任何类型的锁，获准写锁的事务即能读数据也能修改数据
• 用法：select ... for update ;
  在查询语句后使用 for update，会对查询结果的每一行增加增加写锁，当没有其它线程对查询结果集中的任一行数据加锁，会加写锁成功，否则会一直阻塞

9.间隙锁

• 当我们用范围条件而不是用等于条件检索数据，并请求共享或排他锁时，Innodb会给符合条件的已有数据记录的索引加锁，对于键值在范围内但不存在的记录，叫做“间隙(GAP)”。
  Innodb也会对这个间隙加锁，这种锁机制为间隙锁
• 间隙锁危害：
  当锁定一个范围键值后，即使某些不存在的键值也会被锁定，导致这些键值对应的数据无法插入，某些场景下会对性能有很大危害

10.行锁参数分析

• 表锁参数：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_row_lock_current_waits | 0       |
| Innodb_row_lock_time          | 5072647 |
| Innodb_row_lock_time_avg      | 35      |
| Innodb_row_lock_time_max      | 51671   |
| Innodb_row_lock_waits         | 142862  |
+-------------------------------+---------+
5 rows in set (0.00 sec)

Innodb_row_lock_current_waits：当前正在等待锁的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花费的时间
Innodb_row_lock_time_max：等待最长花费的时间
Innodb_row_lock_waits：系统启动到现在等待的总次数

11.行锁结论
读锁：当前加锁线程能读、不能写，其它线程能读、不能写(当读锁被释放，可写成功)
读锁：当前加锁线程能读、能写，其它线程的读写操作会被阻塞(当写锁被释放，可读写成功)
innodb实现了行级锁定，行锁比表锁带来更高的性能消耗，但行锁的并发处理能力要远远高于表锁

12.行锁优化建议

• 数据检索最好添加索引条件，避免无索引行锁升级为表锁
• 尽量少使用范围检索，避免出现间隙锁
• 控制事务大小，减少锁定的资源量和长度

---