create table mylock(
id int not null PRIMARY KEY auto_increment,
name varchar(20)
)engine myisam;
insert into mylock(name)values('a');
insert into mylock(name)values('c');
insert into mylock(name)values('d');
insert into mylock(name)values('e');
insert into mylock(name)values('b');
表锁(MYISAM)
手动添加表锁:
lock table 表名字 read(write),表名字2 read(write),其他;
查看表上加过的锁:
show open tables;
给mylock表加读锁,book表加写锁:
lock table mylock read,book write;
释放表锁:
unlock tables;
读锁(共享):
上锁
lock table mylock read;
session1上读锁 session2可读
session1此时读可以 不可写:
update mylock set name =‘a2’ where id=1;
此时session1读其他表,不可读:
select * from book
session2写mylock表,一直阻塞,只有session释放锁后才能插入成功:
session1(枷锁) | session2 |
---|---|
当前session可以查询该表记录 | 其他session也可以查询该表记录 |
– | – |
当前session不能查询其他没有锁定的表 | 其他session可以查询或者更新未锁定的表 |
– | – |
当前session中插入或者更新锁定的表都会提示错误 | 其他session插入或者更新锁定表会一直等待获得锁 |
– | – |
释放锁 | session2获得锁,插入操作完成 |
写锁(独占)
session1枷锁:lock table mylock write;
session1可读 可写 不可读其他表
session1 | session2 |
---|---|
获得表mylock的WRITE锁定 | 待session1开启写锁后,session1再连接终端 |
当前session对锁定表的查询+更新+插入操作都可以执行 | 其他session对锁定表的查询被阻塞,需要等待锁被释放 |
释放锁 | session2获得锁,查询返回 |
总结:
MYISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
MYSQL的表级锁有两种模式:
锁类型 | 可否兼容 | 读锁 | 写锁 |
---|---|---|---|
读锁 | 是 | 是 | 否 |
写锁 | 是 | 否 | 否 |
结论:
结合商标 所以对MYISAM表进行操作,会有一下情况:
1.对MYISQM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求,只有当读锁释放后,才会执行其他进程的操作
2.对MYISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作
如何分析表锁定:
show status like ’table%‘;
执行后会出现两个变量:
Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
Table_locks_waited:出现表级锁定争用而发生的等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着比较严重的表级锁争用情况
行锁
偏向InnoDB存储引擎,开销大,枷锁慢 会出现死锁;锁定粒度小,发生锁冲突概率最低,并发高;支持事务
查看当前数据库事务的隔离级别:show variables like’tx_isolation’
create table test_innodb_lock (a int(11),b varchar(16))ENGINE=INNODB;
insert into test_innodb_lock value(1,'b2');
insert into test_innodb_lock value(2,'3');
insert into test_innodb_lock value(3,'5');
insert into test_innodb_lock value(4,'4');
insert into test_innodb_lock value(5,'5');
insert into test_innodb_lock value(6,'6');
insert into test_innodb_lock value(7,'7');
insert into test_innodb_lock value(8,'8');
insert into test_innodb_lock value(9,'9');
insert into test_innodb_lock value(1,'10');
CREATE index test_innodb_a_ind on test_innodb_lock(a);
CREATE index test_innodb_b_ind on test_innodb_lock(b);
建表 插入数据 添加索引
关闭事务的自动提交:set autocommit=0;
启动两个session:
session1 修改a=4 的内容 不提交
session2 也修改:
不可修改。
session2再次修改,被阻塞,session1此时提交,提交后session2修改成功
如果session1修改a=4这条数据 不提交 此时session2修改a=9数据 不会被阻塞 锁是被加在行上的
无索引行锁升级为表锁
a为int型 b为varchar b不加单引号来执行强制转换使索引失效
在另一个session中更新其他行数据:
发现被阻塞:
此时行锁升级为了表锁
间隙锁
session1:update test_innodb_lock set b=‘0629’ where a>1 and a<6;
session2:
修改区间内的数据 被阻塞
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,Innodb会给符合条件的已有数据加锁,对于键值在条件范围内但并不存在的记录 叫做间隙
危害:
query执行过程中通过范围查找的话,会锁定整个范围内所有的索引键值,即使这个索引不存在
当锁定一个范围键值后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据,在某些场景下这可能会对性能造成很大危害
如何锁住一行
加for update 这行数据就会被锁住
其他session对这行数据操作会被阻塞
如何分析行锁定:
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:
show status like’innodb_row_lock%';
变量名说明:
innodb_row_lock_current_waits: 当前正在等待锁定的数量
innodb_row_lock_time:从系统启动到现在锁定总时间长度
innodb_row_lock_time_avg:每次等待所花平均时间
innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
innodb_row_lock_waits:系统启动后到现在总共等待的次数
加粗为比较重要的