根据网络资料学习并整理
一、
MyISAM并发插入
#语法:lock tables [tablename] read/write Local
#说明:
1、local的作用:在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾并发插入记录
2、在用lock tables显示加锁时
- 必须同时取得所有涉及到的表的锁
- 不支持锁升级,也就是说只能访问显示加锁的这些表,不能访问未加锁的表
- 如果加的是[读锁],那么当前事务和其他事务只能执行查询操作,不能执行更新|插入|删除操作
- 如果加的是[写锁],那么其他事务不能查询|更新|插入|删除操作。
- 若同一个表在SQL语句中出现多次,那么也需要将SQL语句中相同表的表别名进行锁定,否则通过[别名]访问也会出错
mysql> lock table user as a read,user as b read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user a,user b where a.id=b.id;
+----+---------+-----------+----+---------+-----------+
| id | user_no | user_name | id | user_no | user_name |
+----+---------+-----------+----+---------+-----------+
| 1 | 2 | guan | 1 | 2 | guan |
| 3 | 3 | NULL | 3 | 3 | NULL |
| 4 | 2 | NULL | 4 | 2 | NULL |
| 5 | 7 | NULL | 5 | 7 | NULL |
| 9 | 7 | NULL | 9 | 7 | NULL |
| 10 | 7 | NULL | 10 | 7 | NULL |
+----+---------+-----------+----+---------+-----------+
6 rows in set (0.00 sec)
#若未给别名也加锁,会报错,如下:
mysql> lock table user read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user a,user b where a.id=b.id;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES
mysql>
3、除了显示加锁,还能[隐式加锁],下面几种情况也会加上表锁
session1中给user表
插入/删除/更新
记录时,session2中用lock tables user read命令显示锁表会阻塞,直到session1提交事务
4
、查询表级锁争用情况,
Table_locks_waited比较高,那么说明存在严重的表级锁争用情况
mysql>
show status like'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 133 |
| Table_locks_waited | 0 |
| Table_
open_cache_hits | 15 |
| Table_open_cache_misses | 6 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)
[补充]行级锁争用情况查询:
show status like 'innodb_row_lock%'
mysql>
show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
|
Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 98982 |
| Innodb_row_lock_time_avg | 32994 |
| Innodb_row_lock_time_max | 51036 |
| Innodb_row_lock_waits | 3 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 107017 |
| Innodb_row_lock_time_avg | 35672 |
| Innodb_row_lock_time_max | 51036 |
| Innodb_row_lock_waits | 3 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
5、MyISAM并发插入
#系统变量
- concurrent_insert:0 >>不允许并发插入
- concurrent_insert:1 >>若表中间没有空洞(没有被删除的行),则允许另外一个线程在表尾并发插入?对空洞不是很理解
- concurrent_insert:2 >>无论有没有窑洞,都允许另外一个线程在表尾并发插入
#如何实现?加local选项
语法:lock tables user read | write
local
session1 | session2 | |
lock tables user read | write local | ||
当前session1不能对锁定的表进行
更新|插入|删除
操作
mysql> insert into user values(25,'6',null);
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql> update user set user_no=25 where id=25;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql> delete from user where id=25;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
|
session2可以查询该表的记录
select * from user;
+----+---------+-----------+
| id | user_no | user_name |
+----+---------+-----------+
| 1 | 2 | guan |
| 3 | 3 | NULL |
| 22 | 6 | NULL |
| 25 | 6 | NULL |
| 24 | 6 | NULL |
+----+---------+-----------+
13 rows in set (0.00 sec)
| |
当前session1不能查询没有锁定的表
mysql> select * from student;
ERROR 1100 (HY000): Table 'student' was not locked with LOCK TABLES
|
session2可以进行[
插入
]操作,但是[
更新|删除
]会阻塞
mysql> insert into user values(27,7,'hui');
Query OK, 1 row affected (0.01 sec)
| |
当前session1不能访问session2插入的记录
mysql> select * from user;
+----+---------+-----------+
| id | user_no | user_name |
+----+---------+-----------+
| 3 | 3 | NULL |
| 4 | 2 | NULL |
| 5 | 7 | NULL |
| 9 | 17 | NULL |
| 10 | 7 | NULL |
| 15 | 15 | NULL |
| 19 | 6 | NULL |
| 20 | 6 | NULL |
| 21 | 6 | NULL |
| 22 | 6 | NULL |
| 25 | 6 | NULL |
| 24 | 6 | NULL |
+----+---------+-----------+
12 rows in set (0.00 sec)
|
二、间隙锁问题分析(行锁是给索引加锁)
概念:什么是间隙锁?
与oracle不同的是,mysql加锁主要是对索引加锁(二级索引上间隙锁)
在进行删除或者修改操作时,如果过滤条件列是【非唯一索引】,为了保证当前读的数据一致性,mysql通过间隙锁对数据之间区域进行锁定。(实际上是通过锁定索引达到效果)
这种锁叫间隙锁,这种锁定会造成许多误杀,很多并不冲突的数据会因为间隙锁而无法插入
S锁:共享锁>>lock in share mode
X锁:排他锁>>for update
,除了for update显示加锁,在更新|删除|插入默认会加上排他锁。
##表结构:主键>>id;二级索引>>number
mysql> select * from student;
+----+--------+
| id | number |
+----+--------+
| 10 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 7 | 5 |
| 8 | 88 |
+----+--------+
session1 | session2 |
通过
select * from student where number=5 for update;
加上排他锁
注:number=5的记录是存在的,所以是记录锁?好像也不对呀
1、number=3,number=4时可以更新|删除,但是不能[插入]
2、更新|删除 number=2的记录
(
set id=5)
,会阻塞
综合案例,select ...for update给number=5的间隙也加上了锁
|
select * from user where
number
=5 for update 阻塞
》》》
阻塞
insert into student values(6,5);
insert into student values(4,5);
insert into student values(4,4);
insert into student values(12,9);
》》》
阻塞
delete from student where
number
=5;
》》》
阻塞,删除的记录包含在(3,5),(5,5),(5,88)的间隙之间
delete from student where
number
=3;
delete from student where
number
=4;
注:number=4也可以删除
》》》
执行成功
,
临界值
3,可以删除
update student set
id=88
where
number
=5;
update student set
id=2
where number=5;
》》》
阻塞
update student set id=2 where number=4;
》》》
执行成功
insert into student values(4,2);
》》》
执行成功
更新|删除 number=2的记录
(
set id=5)
,会阻塞
|
delete from student where id=8;
更新|删除|插入会默认加上行锁,即使记录不存在
|
select * from student where id=8 for update;
insert into student values(8,8);
update student set number=8 where id=8;
delete from student where id=8;
》》》以上
查询|插入|更新|删除
的四种操作中,不论
id=8的记录存不存在,都
阻塞
|
间隙锁问题:锁定不存在的记录,那么会将不存在记录的前面和后面的可疑数据都加锁。
如下SQL中,id=9的记录不存在,那么会给id=8,id=10的记录上锁
mysql> select * from student;
+----+--------+
| id | number |
+----+--------+
| 1 | 2 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 7 | 5 |
| 8 | 8 |
| 10 | 1 |
+----+--------+
7 rows in set (0.00 sec)
mysql> select * from student where id=9 for update;
|
update student set number=88 where id=8;
更新|删除|插入
id=8的记录都是阻塞状态
|