MySQL锁基础

一 锁概念

• 通常的锁范围

1. 全局锁(global lock)
2. 表锁(table lock)
3. 行锁(row lock)

• innodb行锁范围

1. record lock
2. gap lock
3. next-key lock

二 加锁对数据库的影响

1.锁等待 

锁L1锁定某个对象R1,锁L2等待锁释放,如果不释放,会一直等待,或者达到系统预设的超时阈值后报错.回滚整个事务或只回滚当前的SQL.

2.死锁

锁资源请求产生了回路.

 

三 myisam 锁

读锁:LOCK TABLE user READ，自身只读，不能写；其他线程可读，不能写。多个线程都可提交read lock 

create table t3_myisam
(
id int PRIMARY key,
name varchar(10)
) ENGINE=myisam;

session1:
root@localhost:mysql.sock  20:14:56 [school]>lock table t3_myisam read;
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql.sock  20:15:23 [school]>insert into t3_myisam(id,name) values(1,'s01');
ERROR 1099 (HY000): Table 't3_myisam' was locked with a READ lock and can't be updated

session2:
root@localhost:mysql.sock  20:22:19 [school]>lock table t3_myisam read;
Query OK, 0 rows affected (0.00 sec)

写锁:LOCK TABLE user WRITE ，自身可读写；其他线程完全不可读写.

写锁优先级高于读锁

SELECT自动加读锁（共享锁）

释放锁，UNLOCK TABLES

session1:
root@localhost:mysql.sock  20:26:51 [school]>lock table t3_myisam write;
Query OK, 0 rows affected (7.91 sec)
root@localhost:mysql.sock  20:27:20 [school]>insert into t3_myisam(id,name) values(1,'s01');
Query OK, 1 row affected (0.08 sec)
session2:
查询就会等待

 特例：单线程往MyISAM表最后空闲位置串行写入新数据不被锁（而写入中间的空洞位置还是会加锁的） 

 

四 innodb锁

默认是行锁（row lock）
Innodb通过在索引记录上加锁，实现行锁.因此,没有索引时就无法实现行锁，而升级成全表记录锁,等同于表锁

1.锁类型
　　a、共享锁
　　b、排他锁
　　c、意向锁，InnoDB特有,加载在表级别上的锁 (防止读写过程中,有DDL发生)

2.其他锁

2.1 全局锁

global read lock:全局读锁 FLUSH TABLES WITH READ LOCK,释放锁 unlock tables

query cache lock:全局query cache锁，最好关闭

对QC中的数据有更新时，都会引发query cache lock

状态：Waiting for query cache lock

如何关闭query cache?
query_cache_size = 0 & query_cache_type = 0

如何清空query cache?

reset query cache;或者 flush tables;

 

2.2 mdl表锁(meta data lock)

事务内的表级锁,5.5开始引入

5.6.6前，事务开启后，会锁定表的meta data lock，其他会话对表有DDL操作时，均需等待mdl释放后方可继续

5.6.6后，不再阻塞其他会话执行DDL，但原来的会话再次访问数据表时，会有error提示：Table definition has changed, please retry transaction

超时阈值定义：lock_wait_timeout

如果想做在线的DDL,一定要用pt-osc工具代替人工online ddl

2.3 自增互斥量(multex) 用来管理自增值Auto-increment

其实是个轻量级的互斥量（MUTEX）

相关选项 innodb_autoinc_lock_mode

root@localhost:mysql.sock  21:19:08 [school]>show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.01 sec)

1 :默认设置，可预判行数时使用新方式，不可预判时仍旧使用表锁，会造成autoinc列自增空洞，不过影响很小

0 :即沿用旧的表级锁模式，每次请求都会等待表锁，不过也非常快。不会影响整个事务，只影响当前的INSERT语句

2 :直接全部使用新方式，不安全，不适合replication环境

2.4 innodb自旋锁,spinlock

在高并发的环境中,线程为了获取CPU资源,而不停的自旋等待

InnoDB spin lock 自旋锁 ,参数innodb_spin_wait_delay，控制轮训间隔，默认6秒

root@localhost:mysql.sock  21:19:17 [school]>show variables like 'innodb_spin_wait_delay';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_spin_wait_delay | 6     |
+------------------------+-------+
1 row in set (0.00 sec)

Show engine innodb status\G

Mutex spin waits 5870888, rounds 19812448, OS waits 375285

事务并发非常高，CPU忙不过来的时候，事务处于sleep状态，spin round可能也会很高

获得mutex的过程：

尝试获取mutex锁，如果已经被其他人锁定了，则会不断尝试：你的锁空出来了吗（这个过程叫做spin wait），多次尝试后，发现还是不行就放弃抵抗进入sleep状态，直到这个mutex 锁被释放了。

** Mutex spin waits 5870888，线程尝试获取spin锁而不可得的次数，也就是 spin-wait 的次数
** rounds 19812448 ，线程进入spin wait循环的次数，也就是检查 mutex 锁的次数
** OS waits 375285，线程放弃spip wait尝试，直接进入sleep状态的次数

 

3.共享锁

共享锁，不允许其他事务修改被锁定的行，只能读

共享锁的产生:

SELECT … LOCK IN SHARE MODE 

或者 在事务中的简单SELECT

 

不在事务中的SELECT是一致性非锁定读，不加锁

 

4.排它锁

锁范围:可能是record lock、next-key lock或者可能只有gap lock

对于update,delete,insert 操作时,innodb会自动给涉及的数据集加排它锁

对于普通的select语句(不在事务的select)是一致性非锁定读,Innodb可以通过以下语句显示的给记录集加共享锁或排他锁

注意:如果在事务中begin select  没有显示的给select加lock in share mode 或者 for update,也是普通的select .

共享锁:

Select * from tablename where … lock in share mode;

排它锁:

Select * from tablename where … for update;

innodb事务锁等待,参数 innodb_lock_wait_timeout      默认值:50秒

5.InnoDB锁之意向锁

IS，事务T想要获得表中某几行的共享锁
IX，事务T想要获得表中某几行的排他锁

意向锁是加载在数据表B+树结构的根节点，也就是对整个表加意向锁

意向锁的作用：避免在执行DML时，对表执行DDL操作，导致数据不一致

 

 

innodb锁兼容性

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容