title: MySQL锁
参考资料:
innodb-locking-transaction-model
1、概述
不同存储引擎支持不同的锁机制。
-
MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);
-
BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;
-
InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但是默认情况下采用行级锁。
表级锁、行级锁、页面锁
-
表级锁:开销小,加锁快;不会出现死锁;锁粒度大。发生冲突几率最大,并发度最低。
-
行级锁:开销大,加锁慢;会出现死锁,发生锁冲突几率最低,并发度最高。
-
页面锁:介于表级锁和行级锁之间。
查看当前数据库支持的引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
查看当前默认的引擎
mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
修改数据库当前引擎
alter table table_name engine=MyISAM;
查看指定表当前的引擎
show table status where NAME ='table_name'
修改mysql默认的数据库引擎
打开配置文件my.ini,将“default-storage-engine=MYISAM”改为你想设定的,然后重启即可
2、MyISAM表级锁
Mysql的表级锁有两种模式:表共享锁(Table Read Lock)和表独占锁(Table Write Lock).
2.1、共享读锁
lock table 表名 read
unlock table table_name
现在用两个窗口模拟多个client请求mysql,当开启共享读锁时,其他client在修改表数据时,进入等待状态。
解除共享读锁后,其他client才会进行提交操作。
2.2、表独占锁
lock table 表名 write
unlock table table_name
现在用两个窗口模拟多个client请求mysql,当开启表独占锁时,其他client在查询表数据时,进入等待状态。
解除表独占锁后,其他client才会进行提交操作。
2.3、查询表级锁使用情况
show status like 'table%'
show variables like '%table%'
Table_locks_immediate:产生表级锁定的次数;
Table_locks_waited:出现表级锁定争用而等待的次数。
两个参数值都是系统启动后开始记录的,出现一次对应的事件,则数量加一。当Table_locks_waited状态值比较高时,那么说明系统中表级锁定争用现象比较明显。
2.4 别名
表名以别名的形式进行操作时,需要在锁操作时添加别名lock table 表名 as 别名 write
2.5 session和锁
一个session使用lock table
命令给表加了锁,这个session可以查询锁定表中的记录,但是更新或访问其他表都会提示错误;
同时,另外一个session可以查询表中的记录,但更新就会出现锁等待,查询其他表数据不受影响。
2.6 并发插入
- 当concurrent_insert=0时,此时读不能与insert写共存。
- 当concurrent_insert=1时,如果表中没有空数据块时(即表中没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入数据。(默认设置)。
- 当concurrent_insert=2时,不论有没有空数据块,都允许在表尾插入数据。
2.6.1 查看当前设置状态
mysql> show variables like 'concurrent_insert';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | AUTO |
+-------------------+-------+
1 row in set (0.00 sec)
2.6.2 设置并发插入状态
mysql> set global concurrent_insert = 2;
Query OK, 0 rows affected (0.00 sec)
2.6.3 举例并发插入状态为2(ALWAYS)时
show variables like 'concurrent_insert';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| concurrent_insert | ALWAYS |
+-------------------+--------+
1 row in set (0.00 sec)
现在用两个窗口模拟多个client请求mysql,当client_A开启并发插入时,
mysql> lock table student read local;
Query OK, 0 rows affected (0.00 sec)
client_B在操作表数据时,可以直接修改表中数据。
但是此时client_A对该表进行查询操作时,表中数据依然为未修改数据;而其他client为已修改数据。
2.7 一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一个表的写锁,mysql如何处理?
写进程先获得锁,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁队列。
Mysql认为写请求写请求比读请求更重要。
MyISAM不太适合有大量更新操作和查询操作的原因,因为大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。
解决方法:
执行`set low_priority_updates=1`,使该连接发出的更新请求优先级降低。同理insert,delete也可以通过此方法指定。
另外MySQL也提供了一种折中的方法调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定的获取锁的机会
3 Innodb锁
关闭自动提交 `set autocommit=0;`
- 共享锁(S)
- 排它锁(X)
- 意向共享锁(IS)
- 意向排他锁(IX)
排它锁(X) | 意向排他锁(IX) | 共享锁(S) | 意向共享锁(IS) | |
---|---|---|---|---|
排它锁(X) | 冲突 | 冲突 | 冲突 | 冲突 |
意向排他锁(IX) | 冲突 | 兼容 | 冲突 | 兼容 |
共享锁(S) | 冲突 | 冲突 | 兼容 | 兼容 |
意向共享锁(IS) | 冲突 | 兼容 | 兼容 | 兼容 |
3.1 意向锁
3.1.1 意向锁
对于update、delete和insert语句,Innodb会自动给涉及数据集加排他锁(X);
对于普通的select语句,Innodb不会加任何锁。
事务可以通过以下语句显式的给记录集加锁:
//共享锁
select * from table_name where ... lock in share mode;
//排它锁
select * from table_name where ... for update;
3.1.2 行锁变表锁
现在用两个窗口模拟多个client请求mysql,当client_A对表中某一行数据进行修改时,触发了索引失效。
client_A未进行commit
时,则client_B对该表中其他行数据进行修改时,由于mysql数据库对name
类型转换进行优化时,发生了表锁事件。
注释:实例中 `name`字段为`varchar`类型的索引,在更新操作时字段赋值为 666(而不是'666') ,此时mysql数据库会自动将其类型转化。
转化条件需要设置sql模式为非严格模式
mysql> show session variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)
3.1.3 间隙锁(gap lock)
InnoDB使用间隙锁的目的:
- 防止幻读,以满足相关隔离级别的要求;
- 满足恢复和复制的需要;
间隙锁定是对索引记录之间的间隙的锁定,或者是对第一个或最后一个索引记录之前的间隙的锁定。
例如,SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
防止其他事务将value 15插入column中t.c1,无论该列 中是否已有这样的值,
因为该范围中所有现有值之间的间隙都是锁定的。
间隙可能跨越单个索引值,多个索引值,甚至为空。
对于使用唯一索引来锁定唯一行来锁定行的语句,不需要间隙锁定。
(这不包括搜索条件仅包含多列唯一索引的某些列的情况;在这种情况下,会发生间隙锁定。)
例如,如果该id列具有唯一索引,则以下语句仅使用一个具有id值100 的行的索引记录锁定,
其他会话是否在前面的间隙中插入行并不重要:
SELECT * FROM child WHERE id = 100;
间隙锁定InnoDB是“ 纯粹抑制性的 ”,这意味着它们的唯一目的是防止其他事务插入间隙。
3.1.4 InnoDB使用索引的条件
-
在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。
-
mysql的行锁是针对索引加的锁。不是针对记录加的锁,虽然是访问不同的行,但是若是相同的索引,会出现锁锁冲突的。
当表中含有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行。 -
即使在条件中使用了索引,但是是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价决定的,如果MySQL认为全表扫描效率更高,比如很小的表,他也不会使用索引,此时InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突的时候,不要忘记检查SQL的执行计划,以确定是否真正使用了索引。
关于InnoDB到底是使用行锁还是表锁,我们需要依据索引来决定的,本质上行锁是针对索引加的锁,而非记录!!!
虽然是访问不同的行,但是若是含有相同的索引,还是会发生锁冲突的!!!
而且就算条件里面使用了索引,Mysql也不一定走索引,还是要看SQL的执行计划!!!
参考资料: