Mysql之锁机制
1. 锁的介绍
锁是计算机协调多个进程或线程并发访问某一资源的限制
2. lock与latch
1. latch
latch一般称为闩锁，latch分为mutex和rwlock，其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制
2. lock
lock的对象是事务，用来锁定的是数据库中的对象，如表，页，行。并且一般lock的对象仅在事务commit或rollback后进行释放
lock具有死锁机制的
3. 比较
4. 查看latch
show engine innodb mutex;
结果：
3. 锁的分类
1. 从对数据操作的类型分类
共享锁（S Lock读锁） 针对同一份数据，多个读操作可以同时进行而不会相互影响，只能读数据，不能写数据
排他锁（X Lock写锁） 当前写操作没有完成前，它会阻断其它写锁和读锁
意向锁（I Lock意向锁） 事务系统在更细粒度上进行加锁
2. 从对数据操作的粒度分类
表锁（偏读） 偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行锁（偏写） 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高
4. 锁的兼容性
如果需要对页上的记录r上X锁，那么分别需要对数据库,表，页上意向锁IX,最后对记录r上X锁。
5. 锁信息的查看
1. 通过命令
show engine innodb status\G;
2. 通过表查看
information_schema库下的INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS
1. innodb_trx
trx_id：事务ID。只读事务和非锁事务是不会创建id的。 trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。 trx_started：事务开始时间。 trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。 trx_wait_started：事务开始等待的时间。 trx_weight：事务的权重。代表修改的行数和被事务锁住的行数。为了解决死锁，innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。 trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。 trx_query：事务正在执行的 SQL 语句。 trx_operation_state：事务当前操作状态。 trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。 trx_tables_locked：当前执行 SQL 的行锁数量。因为只是行锁，不是表锁，表仍然可以被多个事务读和写 trx_lock_structs：事务保留的锁数量。 trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。 trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。 trx_rows_modified：事务更改的行数。 trx_concurrency_tickets：该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。 trx_isolation_level：当前事务的隔离级别。 trx_unique_checks：是否打开唯一性检查的标识。 trx_foreign_key_checks：是否打开外键检查的标识。 trx_last_foreign_key_error：最后一次的外键错误信息。 trx_adaptive_hash_latched：自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制，默认值为8。 trx_adaptive_hash_timeout：是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0
2. innodb_locks
lock_id：锁 ID。 lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。 lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。 lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。 lock_table：被锁定的或者包含锁定记录的表的名称。 lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。 lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。 lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。 lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。 lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。
3. innodb_lock_waits
requesting_trx_id：请求事务的 ID。 requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。 blocking_trx_id：阻塞事务的 ID。 blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。
6. 锁的特性
1. 一致性非锁定读
InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会去等待行上锁的释放，InnoDB存储引擎回去读行的一个快照数据。
在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。
在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
2. 一致性锁定读
SELECT ... FOR UPDATE 对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁
SELECT ... LOCK IN SHARE MODE 对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。
3. 自增长与锁
4. 外键和锁
7. 锁的算法
1. Record Lock
单个行记录上的锁
总是会锁住索引信息，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，会使用隐式的主键来锁定。
2. Gap Lock
间隙锁，锁定一个范围，但不包含记录本身
1. 什么是间隙锁
当我们用范围条件而不是相等条件检索数据，并请求共享锁或排他锁时，Innodb会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内不存在的记录，叫做间隙(GAP)；
Innodb也会对这个间隙加锁，这种锁机制就叫做间隙锁。
2. 危害
通过范围更新语句，会对范围内的记录的索引加锁，即使这个记录不存在，也会加锁，可能会影响正常业务，还有服务器性能。
例子：
1. 第一个会话执行更新语句
update test_lock_innodb set b='10000' where a>1 and a<6;
2. 第二个会话执行插入语句
insert into test_lock_innodb (a,b) values (3,"houzi");
结果发生阻塞
3. 第一个会话commit,第二个会话阻塞结束
4. 第二个会话commit;
3. Next-Key Lock
锁定一个范围，并且锁定记录本身
next-key锁技术包含了记录锁和间隙锁，next-key是为了防止发生幻读，而只有RR隔离级别以上才能防止幻读
3. MyISAM读锁分析
1. 创建一个MyISAM的表
Create Table: CREATE TABLE `mylock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2. 手动增加一个锁表
lock table 表名称 read(write),表名称2 read(write),其它表；
3. 查看表上加过的锁
show open tables;
4. 在mylock表上锁
lock table mylock read;
show open tables where in_use>=1;
可以看到mylock。
5. 释放锁表
unlock tables;
结论：开启读锁后，当前会话可以读锁表，不能修改锁表，不能读其它的表；其它会话可以读锁表，不能修改锁表（并且会发生阻塞），可以读其它表。
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
ERROR 1100 (HY000): Table 'user' was not locked with LOCK TABLES
4. MyISAM写锁分析
1. 手工添加一个写锁
lock table mylock write;
2. 读当前会话的锁表
select * from mylock; 可以读取
3. 插入当前会话的锁表
insert into mylock (name) values ('g');
4. 当前会话读取其它的表
ERROR 1100 (HY000): Table 'user' was not locked with LOCK TABLES
5. 其它会话读锁表
会发生阻塞
结论：开启写锁后，当前会话可以增删改查锁表，不能读其它的表；其它会话读锁表，会阻塞。
5. 查看哪些表加锁
show open tables where in_use >=1;
6. 分析表锁情况
show status like 'table%';
7. 行锁案例分析
1. 创建一个表，引擎为innodb
create table test_lock_innodb (a int,b varchar(10)) engine innodb;
insert into test_lock_innodb (a,b) values (1,"yangjianbo");
insert into test_lock_innodb (a,b) values (2,"yichangkun");
insert into test_lock_innodb (a,b) values (3,"houzi");
insert into test_lock_innodb (a,b) values (4,"jiangzhong");
create index test_lock_innodb_a on test_lock_innodb(a);
create index test_lock_innodb_b on test_lock_innodb(b);
2. 开启两个会话，都关闭自动提交。
set autocommit=0;
3. 第一个会话修改第三条数据
update test_lock_innodb set b='yuanshao' where a=3;
4. 第二个会话查看一下数据，未发生改变
5. 将第一个会话commit一下，第二个会话还是没有改变。
6. 将第二个会话也commit一下，第二个会话就发生改变了。
7. 第一个会话再修改一下，第二个会话也修改，第二个会话会发生阻塞。同时Innodb_row_lock_waits这个计数器也会增加1。
8. 第一个会话commit一下，第二个会话阻塞结束。
9. 第二个会话然后commit一下，然后第一个会话再commit一下，两个会话的数据保持一致。
10. 第一个会话修改第三条数据，第二个会话修改第四条数据,不会发生阻塞。
8. 索引失效行锁变表锁
1. test_lock_innodb表中a字段为int,b字段为varchar类型。
2. 修改varchar字段的时候，必须使用单引号或者双引号。否则会导致索引失效。
例子：
1. 第一个会话修改a字段
update test_lock_innodb set a=5 where b=3000; 因为b字段是varchar类型，所以需要加单引号或者双引号，如果不加引号，会导致索引失效。
2. 第二个会话修改b字段
update test_lock_innodb set b='5000' where a=4;
结果导致该修改语句，阻塞。
3. 第一个会话commit，第二个会话阻塞结束。
4. 第二个会话commit，两边数据保持一致。
5. 第一个会话commit。
结论： 发现Innodb_row_lock_waits这个计数器加1。
10. 如何锁定一行
1. 第一个会话
begin;
select * from test_lock_innodb where a=3 for update;
2. 第二个会话
update test_lock_innodb set b='yuanshao' where a=3; 发生阻塞，因为第一个会话使用for update，锁定了这一行
3. 第一个会话commit一下,第二个会话的阻塞结束。
4. 两个会话都分别执行一下commit，数据一致。（因为我关闭了自动提交功能）
11. 行锁总结
1. 查看行锁状态
show status like '%lock%';
Innodb_row_lock_current_waits 0 当前正在等待锁定的数量 Innodb_row_lock_time 31541111 从系统启动到现在锁定总时间长度 Innodb_row_lock_time_avg 56 每次等待花费的时间 Innodb_row_lock_time_max 304221 从系统启动到现在等待最长的一次花费的时间 Innodb_row_lock_waits 562001 从系统启动后到现在总共等待的次数
8. 死锁
1. 产生死锁4个必要条件
禁止抢占
持有和等待
互斥
循环等待
2. 解决死锁的方法
经常提交你的事务，小事务更少倾向于冲突
以固定的顺序访问你的表和行
在表上添加索引
不要把无关的操作放到事务里面
尽量按照主键/索引去查找记录
优化SQL和表设计，减少同时占用太多资源的情况
3. 查看死锁
show engine innodb status\G;
浙公网安备 33010602011771号