表锁

• 偏向myisam存储引擎

优点

• 开销小
• 加锁快
• 无死锁

缺点

• 粒度大
• 锁冲突概率高
• 并发度低

读锁（共享锁）

• 数据表

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');
create table book(
    id int not null primary key auto_increment,
    name varchar(20)
)engine myisam;
insert into book(name) values('a');
insert into book(name) values('b');
insert into book(name) values('c');
insert into book(name) values('d');
insert into book(name) values('e');

写锁（排他锁）

总结

1. session-1加了读锁，则：session-1、session-2都可以读；session-1不可写，session-2写阻塞；session-1不能都其他，session-2可读其他。
2. session-1加了写锁，则：session-1可读，session-2读阻塞；session-1可写，session-2写阻塞；session-1不能读其他，session-2可读其他。
3. MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

其他命令

show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 193   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 4     |
| Table_open_cache_misses    | 2     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set
# Table_locks_immediate：产生表级锁定的次数，表示可以立即获得锁的查询次数
# Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），
                      此值高则说明存在着较严重的表级锁争用情况

行锁

• InnoDB存储引擎默认给我们加了行锁

回顾事务

• 问题
  • 脏读：一个事务读取到了另一个事务未提交的数据
  • 不可重复读：一个事务多次读取同一条记录，读取的结果不一致（读取到另一个事务已提交的数据）
  • 幻读（虚读）：一个事务多次查询的数据条数不一致（读取到另一个事务增加或删除的数据）
• 隔离级别
  • read uncommitted（读未提交）：不做隔离，具有脏读、不可重复读、幻读等问题
  • read committed（读提交）：可避免脏读、不可避免不可重复读、虚读等问题
  • repeatable read（可重复读）：可以避免脏读、不可重复读、虚读，是mysql的默认隔离级别
  • serializable（可串行化）：每个数据加上锁，最高隔离级别，但会出现超时现象，很少应用

测试隔离级别

• 测试数据

create table book(
    id int not null primary key auto_increment,
    name varchar(20)
)engine innodb;
insert into book(name) values('a');
insert into book(name) values('b');
insert into book(name) values('c');

InnoDB是如何解决幻读的？

1. 多版本并发控制（MVCC）（快照读/一致性读）
   多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。以 InnoDB 为例，每一行中都冗余了两个字段。一个是行的创建版本，一个是行的删除（过期）版本。具体的版本号（trx_id）存在 information_schema.INNODB_TRX 表中。版本号（trx_id）随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。普通的 select 就是快照读。
   原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。
2. next-key 锁 （当前读）
   next-key 锁包含两部分：记录锁（行锁）、间隙锁，记录锁是加在索引上的锁，间隙锁是加在索引之间的
   原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的

行锁演示

行锁变表锁

• 索引失效导致
  

间隙锁

其他命令

show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 116128 |
| Innodb_row_lock_time_avg      | 19354  |
| Innodb_row_lock_time_max      | 50436  |
| Innodb_row_lock_waits         | 6      |
+-------------------------------+--------+
5 rows in set
# Innodb_row_lock_current_waits:当前正在等待锁定的数量;
# Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
# Innodb_row_lock_time_avg:每次等待所花平均时间;
# Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
# Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

总结

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