锁问题

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

mysql 锁

1
2
3
4
5
6
7
8
9
10
1.不同引擎支持不同的锁机制
myisam memory table-level locking
BDB (被innodb取代) page-level locking table-level locking
Innodb row-level locking table-level locking

2. 表级锁 开销小,加锁快;不会出现死锁;锁定粒度大,发生概率最高,并发度最低
适合查询为主,少量按索引更新的数据应用
3. 行级锁 开销大,加锁慢;会出现死锁;锁定粒度小,发生概率最低,并发度最高
适合大量按索引条件并发更新不同数据,支持并发查询的应用
4. 页面锁 介于表锁和行锁之间

myisam引擎只支持表锁

1
2
3
// 查看查询表级锁 竞争情况
1. show status like 'table%';
2. information_schema.GLOBAL_STATUS 表
  • 两种表级锁模式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    表共享读锁 table read lock

    表独占写锁 table write lock

    1. myisam 读锁不会阻塞其他用户的读请求,会阻塞其他用户写请求
    2. myisam 写锁会阻塞其他用户对同一表的读写操作
    3. myisam表的读操作与写操作以及写操作之间是串行的
    4. 在执行查询和更新操作前 会自动给涉及表加锁,不需要用户干预
    5. 一般给加显示锁 是为了同一时间点对多个表的一致性读取数据
    eg: lock tables orders read local,order_detail read local;
    6. myisam lock tables 只能访问显示加锁的表,不能访问其他未加锁的表
    7. myisam 总是一次获得sql语句所需的全部锁,不会出现死锁(Deadlock Free)
  • myisam 并发插入

    1
    2
    3
    4
    5
    6
    7
    8
    // 查看myisam 并发插入行为
    1. show VARIABLES like "%concurrent_insert%";
    2. information_schema.GLOBAL_VARIABLES 表

    concurrent_insert 值:
    0 不允许并发插入
    1 默认 没有空洞情况下 允许边读边写
    2 不管有没有空洞,都允许表尾并发插入
  • myisam 锁调度

1
2
3
4
5
6
7
8
9
10

1. myisam 读锁和写锁互斥,读写串行
2. 同时申请写进程先获得锁
// information_schema.GLOBAL_VARIABLES
// show VARIABLES like "low-priority%";

//low-priority-updates 降低更新优先级请求
SET LOW_PRIORITY_UPDATES=1
// 查看 myisam 做大写请求锁数量 或达到这个值后,写锁优先级降低
show VARIABLES like "max_write_lock_count";

innodb 锁

  1. 支持事务 ACID
  2. 采用行级锁
  • 并发事务处理会出现问题

    1
    2
    3
    4
    a. 更新丢失 lost update  更新覆盖
    b. 脏读 dirty reads
    c. 不可重复读 Non-repeatable reads
    d. 幻读 phantom reads
  • 事务隔离级别(数据读取一致性问题)

    1
    2
    3
    1. 读之前加锁,阻止其他事务对数据修改

    2. 不加锁,MVCC or MCC(MultiVersion Concurrency Control) 数据多版本并发控制 (特定机制在某时间点生成数据快照,为用户提供一定级别的一致性读取)
  • 乐观锁与悲观锁

1
2
3
4
5
6
7
8
9
10
11
乐观锁 
非数据库自带的 是应用级别实现的,
思想:乐观的认为操作不会导致冲突,在读之前不加锁,在更新的时候判断是否冲突 ,采用mvcc (核心就是每次更新数据就在当前版本加1)

悲观锁
数据库级别 事务实现
思想:悲观的认为操作会导致冲突,每次操作数据都要获取锁才进行后续处理

相较而言 悲观锁更安全但是需要耗费更多时间

悲观锁的实现由两种 共享和排他
  • 事务隔离级别
1
2
3
4
为解决不同隔离级别来平衡“隔离” '并发'的矛盾 定义4个级别
//查看
select @@tx_isolation
information_schema.GLOBAL_VARIABLES
事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)
  • innodb 行锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
//查看设置
show status like "innodb_row_lock%"
information_schema.GLOBAL_STATUS
/查看锁信息
information_schema.INNODB_LOCKS
information_schema.INNODB_LOCK_WAITS

//设置 monitor 观察锁情况

四种monitor分别是:
//standard monitor
//监视活动事务持有的表锁、行锁;事务锁等待;
//线程信号量等待;文件IO请求;buffer pool统计信息;
//InnoDB主线程purge和change buffer merge活动
innodb_monitor:create table innodb_monitor(x int) engine=innodb;
//提供额外的锁信息
innodb_lock_monitor:create table innodb_lock_monitor(x int) engine=innodb;
5.7>= 移除
innodb_table_monitor:create table innodb_table_monitor(x int) engine=innodb;
5.7>= 移除
innodb_tablespace_monitor:create table innodb_tablespace_monitor(x int) engine=innodb;

DROP TABLE innodbXXX_monitor;

//或者设置

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON

information_schema.GLOBAL_VARIABLES


show engine innodb status;
  • innodb 行锁类型 (实际是对索引加锁)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
共享锁(S) 又称 读锁 阻止其他事务更新操作(只能读不能修改)

排他锁(X) 又称 写锁
对于多个不同的事务,对同一个资源只能有一把锁,获得排他锁的事务可以操作数据,阻止其他事务获取当前数据集的共享读或排他写锁 可读(默认 read-committed)

//查看锁
select * from information_schema.INNODB_TRX

kill -9 mysql-thread-id

# 共享锁
-- begin;
-- select * from tb_en_cart where id=8 lock in share mode;
-- commit;

# 排他锁
BEGIN;
select * from tb_en_cart where id=8 for UPDATE;
update tb_en_cart set quantity=0 where id=8;
commit;

```

+ innodb 内部意向表锁类型

意向共享锁(IS)
事务给数据行加行共享锁前必须获取IS
意向排他锁(IX)
事务给数据行加行排他锁前必须获取IX

意向锁是innodb 自动处理的,不需要干预

1
2

+ innodb 行锁实现方式

record lock 对索引项加锁

gap lock 索引项之间间隙加锁

next-key lock 对记录及前面或后面加锁 (注意 范围查询下并发锁冲突)

备注:
如果没有索引,innodb隐藏的通过聚簇索引对记录加锁,
即意味着不通过索引条件检索数据,那么innodb将对表的所有记录加锁,
效果跟表锁一样,实际应用中可导致大量锁冲突,影响并发性能

1
2
3
4
5
6
7
8
9
10
11
12
13

> 聚簇索引即主键索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序,聚簇索引的索引页面指针指向数据页面。非聚簇索引(即第二主键索 引)不重新组织表中的数据,索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述,那么,聚簇索引的叶节点就是数据节点,而非聚簇 索引的叶节点仍然是索引节点,通常是一个指针指向对应的数据块


#### 恢复和复制 对innodb锁机制的影响

mysql 通过binlog 记录成功更新数据的语句,并由此实现mysql的恢复或者主从复制

+ 三种日志格式四种复制模式

https://dev.mysql.com/doc/refman/5.7/en/replication-rbr-usage.html
https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html
https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-failover.html

三种日志格式

  1. SBL ( statement-based logging ) 基于事务提交先后顺序记录语句,(事务隔离级别最高,不允许幻读)
    eg: 在 insert into tbl select or create table new_tbl select mysql对源表做了共享锁处理只读不可修改
    如果是范围条件 ,innodb还会给源表加 next-key锁
    相关设置:
    innodb_locks_unsafe_for_binlog=off(默认)
    采取措施:
    a. innodb_locks_unsafe_for_binlog=on ,强制mvcc一致性读,但是binlog不能正确恢复或复制数据 (不推荐)
    b. 利用 select * from tbl into outfile 和 load data infile 组合导入
    c. 基于行的binlog格式和基于行的数据复制
  1. RBL ( row-based logging ) 基于行

  2. MFL (mixed-format logging) 混合格式
    四种复制模式

  3. SBR (statement-based replication) 最早支持的复制模式
  4. RBR (row-based replication) 5.1后支持,主要优点是支持对非安全sql的复制
  5. MIXED (mixed-format replication ) 对安全的sql采用基于sql语句复制模式,对于非安全的sql采用基于行的复制模式
    4.使用全局事务ID(GTIDs) 主要解决主从自动同步一致性问题 (查看 select @@global.read_only ;)
1
2
3

#### 不同隔离级别下的一致性读及锁的策略
> 同时还要考虑数据恢复与复制机制

大部分sql而言 ,隔离级别越高,innodb给记录加的锁就越严格(尤其使用范围条件时候),
产生锁冲突的可能越高,从而对并发性事务处理性能影响越大,
实践中,尽量利用较低的隔离级别,以减少锁争抢,一般通过优化 默认 read-committd 足够,
对于特殊的可以在业务逻辑设置 串行化级别隔离

1
2

#### innodb 表锁使用场景(大部分是行锁和事务处理)
  1. 事务需要更新大部分或全部数据,表又较大,如果用行锁,不仅效率低而且容易造成锁等待或者冲突
  2. 事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚,考虑一次性锁定事务涉及的全部表

注意:

  1. lock tables 可以给innodb加表级锁,但是表锁不是有innodb引擎管理而是有上层mysql server 负责 只有 autocommit=0 innodb_table_locks=1 innodb引擎才能自动识别表级别死锁
  2. 加表锁正确姿势
    set autocommit=0;
    lock tables tb1 write,tb2 read
    do somethin
    commit;
    unlock tables;
1
2

#### 关于死锁
  1. myisam 表级锁 是自动的不需要额外干预 ,不会出现死锁 (同时申请 写锁优先)

  2. innodb 会发生死锁问题 (相互持有对方排他锁事务处理,造成循环-锁等待)
    a. innodb 一般能自动检测,是一个事务释放并回滚,另一个事务完成事务
    b. 涉及外部锁或表锁情况 ,不能完全自动检测到,需要设置 innodb_lock_wait_time_out (不仅可以解决死锁还能在并发事务挂起时避免长时间占用资源的阈值)

    一般来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小以及sql优化,是可以避免绝大部分死锁问题

1
2
3


#### 避免死锁的方法
  1. 应用中,不同的程序并发存取多个表,应尽量约定以相同顺序来访问降低死锁机会
  2. 事务中,如果有更新或其他操作 直接申请足够级别的锁 不要先申请共享,再然后申请排他
  3. 不同隔离级别锁机制不同 repeatable-read(对相同记录同时申请排他锁,在没有记录情况下会成功) ->read-commitd (但是也不能完全避免死锁发生)
    `