根据网络资料学习并整理
一、MyISAM并发插入
＃语法：lock tables [tablename] read／write Local
＃说明：
１、local的作用：在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录
２、在用lock tables显示加锁时
- 必须同时取得所有涉及到的表的锁
- 不支持锁升级，也就是说只能访问显示加锁的这些表，不能访问未加锁的表
- 如果加的是［读锁］，那么当前事务和其他事务只能执行查询操作，不能执行更新｜插入｜删除操作
- 如果加的是［写锁］，那么其他事务不能查询｜更新｜插入｜删除操作。
- 若同一个表在ＳＱＬ语句中出现多次，那么也需要将ＳＱＬ语句中相同表的表别名进行锁定，否则通过［别名］访问也会出错
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>
３、除了显示加锁，还能［隐式加锁］，下面几种情况也会加上表锁
session1中给user表插入／删除／更新记录时，session２中用lock tables user read命令显示锁表会阻塞，直到session1提交事务
４、查询表级锁争用情况，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)
５、ＭyISAM并发插入
＃系统变量
- 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通过间隙锁对数据之间区域进行锁定。（实际上是通过锁定索引达到效果）
这种锁叫间隙锁，这种锁定会造成许多误杀，很多并不冲突的数据会因为间隙锁而无法插入
Ｓ锁：共享锁>>lock in share mode
Ｘ锁：排他锁>>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＝５的记录是存在的，所以是记录锁？好像也不对呀 １、number=3,number=4时可以更新｜删除，但是不能［插入］ ２、更新｜删除 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; 》》》阻塞，删除的记录包含在（３，５），（５，５），（５，８８）的间隙之间 delete from student where number=3; delete from student where number=４; 注：number=4也可以删除 》》》执行成功 ，临界值３，可以删除 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=８的记录存不存在，都阻塞 |
间隙锁问题：锁定不存在的记录，那么会将不存在记录的前面和后面的可疑数据都加锁。如下ＳＱＬ中，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的记录都是阻塞状态 |
扫一扫
4575
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
