1. 查看表锁命令
mysql> show open tables;
In_use为0 ,表示没有锁表
2. 锁表
2.1 给表上读锁
lock table tb_emp read;
2.2 给表上写锁
lock table tb_emp write;
2.3 释放锁
unlock tables;
3. 测试
3.1创建表tb_a. tb_b
mysql> create table tb_a(id int not null auto_increment, name varchar(10), primary key(id)) engine myisam;
mysql> create table tb_b(id int not null auto_increment, name varchar(10), primary key(id)) engine myisam;
3.2 添加数据
insert into tb_a(name) value('a');
insert into tb_a(name) value('b');
insert into tb_a(name) value('c');
insert into tb_b(name) value('aa');
insert into tb_b(name) value('bb');
insert into tb_b(name) value('cc');
3.3 session1 给tb_a上读锁
mysql> lock table tb_a read;
Query OK, 0 rows affected (0.00 sec)
session1 会话 可以读tb_a
session1 会话 不可以写tb_a
session1 会话 不可以读tb_b
session2 会话 可以读tb_a,也可以读tb_b;
session2 会话 写tb_a 会阻塞
此时,session1 执行 unlock tables 释放掉读锁, session2 的update 操作才会成功.
3.4 session1 给tb_a上写锁
mysql> lock table tb_a write;
Query OK, 0 rows affected (0.00 sec)
session1 可读 tb_a;
session1 不可读tb_b;
session1 可写tb_a;
session1 不可写tb_b;
session2 读tb_a 阻塞,直到session1 释放tb_a的写锁.
3.5 总结
总而言之,读锁会阻塞写, 但是不会阻塞读;写锁则会把读和写都阻塞
4. 表锁分析
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定.
mysql> show status like 'table%';
Table_locks_immediate : 产生表级锁定的次数. 它表示立即获取锁的查询次数,每立即获取到锁其值加1.
Table_locks_waited : 出现表级锁争抢而发生等待的次数, 每等待一次,其值加1,此值越大,说明系统存在着表锁竞争的情况越严重.
MyISAM引擎,读锁优化, 容易造成永乐阻塞 , 所以适用于偏读的场景 .
5. 行锁
行锁偏向innodb, 开锁大, 加锁慢, 会出现死锁;锁粒度最小,发生锁冲突的概率最小,并发度最高.
Innodb与MyIsam最大区别: 支持事务, 使用行锁
5.1 创建表
create table tb_innodb_lock(id int,name varchar(10)) engine innodb;
insert into tb_innodb_lock(id,name) value (1, 'b1');
insert into tb_innodb_lock(id,name) value (2, '2000');
insert into tb_innodb_lock(id,name) value (4, '4000');
insert into tb_innodb_lock(id,name) value (5, '5000');
insert into tb_innodb_lock(id,name) value (6, '6000');
insert into tb_innodb_lock(id,name) value (1, 'b2');
再建两个索引
alter table tb_innodb_lock add index index_id(id);
create index index_name on tb_innodb_lock(id);
关闭事务的自动提交
set autocommit =0;
正常情况下,因为innnodb是行锁, 所以如何两两个session分别操作id=4, id=5两行是不产生阻塞的情况,即:
session1 : update tb_innodb_lock set name = '4001' where id =4;
session2 : update tb_innodb_lock set name = '5001' where id =5;
但是如果是下面这种情况,索引失效,会让innnodb的行锁转化成表锁,从而产生阻塞.
session1 : update tb_innodb_lock set id = 41 where name = 4000 ;
session2 : update tb_innodb_lock set name = '5001' where id = 5 ;
sesssion1 update操作 索引失效(因为name存在着数据类型的转换), innnodb存储引擎由行锁转换成了表锁,如果这时session1没有commit, session2的update操作就会一直阻塞,直到session commmit.
6. 间隙锁
当我们使用范围条件而不是等值条件检索数据,并请求共享或排它锁时,innodb会给符合条件的已有数据记录的索引项中锁;对于键值在条件范围内但并不存在的记录,叫着"间隔(GAP)", Innodb也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁(NEXT-KEY锁).
危害: 因为query执行过程中通过范围查找的话,他会锁定整个范围内的所有索引键值,即使这个键值并不存在 . 间隙锁有一个致使的缺点, 就是当锁定一个范围的键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据. 这在某些情况下可能非常影响情能.
示例:
mysql> select * from tb_innodb_lock;
+------+------+
| id | name |
+------+------+
| 1 | b1 |
| 2 | 2000 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 1 | b2 |
+------+------+
假设
session1 : update tb_innodb_lock set name = 'cccc' where id > 1 and id < 6;
session2 : insert into tb_innodb_lock (id , name ) value (3,'3000');
session1在操作update时,直接索定了id 2 到 5之间的记录, session2 执行insert 操作就会阻塞,直到session1 commit 方可insert 成功.
7. 行锁分析
可以通过检查innodb_row_lock状态变量来分析系统上的行锁争夺情况.
show status like 'innodb_row_lock%';
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 16938 |
| Innodb_row_lock_time_avg | 4234 |
| Innodb_row_lock_time_max | 4705 |
| Innodb_row_lock_waits | 4 |
+-------------------------------+-------+
Innodb_row_lock_current_waits : 当前正在等待锁定的数量
Innodb_row_lock_time : 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg : 每次等待所花的平均时间
Innodb_row_lock_time_max : 从系统启动到现在等待最长一次的时间
Innodb_row_lock_waits : 系统启动后到现在总共等待次数
