按操作类型分 读锁(共享锁) 对于同一条数据,可多条读数据同时进行,互不干涉 写锁(互斥锁) 如果当前写操作未完成,则无法进行其他的读写操作 按操作范围分 表锁 一次性对一张表加锁 MyISAM默认使用表锁 开销小,加锁快,无死锁,但锁的范围大,容易发生锁冲突,并发度低 行锁 一次性对一条数据加锁 InnoDB默认使用行锁 开销大,加锁慢,容易出现死锁,锁的范围较小,不容易发生锁冲突,并发度高(很小发生并发问题:脏读、幻读、不可重复读、丢失更新等问题) 比表锁性能损耗大 并发能力强,效率高 页锁 建议:高并发采用InnoDB,否则采用MyISAM
-- 自增操作 MySQL/SQL Server 支持,Oracle 需要借助序列来实现自增 create table tablelock( id int primary key auto_increment, name varchar(20) )engine myisam; insert into tablelock(name) values('a1'); insert into tablelock(name) values('a2'); insert into tablelock(name) values('a3'); insert into tablelock(name) values('a4'); insert into tablelock(name) values('a5');
增加锁:
lock table 表1 read/write, 表2 read/write, ....
查看加锁的表:
show open tables;
会话:session
释放锁
unlock tables;
会话0: lock table tablelock read; select * from tablelock; -- 读(查),可以 delete from tablelock where id=1; -- 写(增删改),不可以 select * from emp; -- 读,不可以 delete from emp where eno = 1 ; -- 写,不可以 会话1(其他会话): select * from tablelock; -- 读(查),可以 delete from tablelock where id=1; --写,会"等待"会话0将锁释放 会话1(其他会话): select * from emp; -- 读(查),可以 delete from emp where eno=1; --写,可以 -- 如果某一个会话对A表加了read锁, -- 则该会话可以对A表进行读操作、不能进行写操作;且该会话不能对其他表进行读、写操作。(即如果给A表加了读锁,则当前会话只能对A表进行读操作。) -- 其他会话,对A表可以读,写需要等待锁释放;可以对其他表(A表以外的表)进行读、写操作
会话0: lock table tablelock write;
当前会话(会话0)
可以对加了写锁的表 进行任何操作(增删改查);但是不能 操作(增删改查)其他表
其他会话:
对会话0中加写锁的表 可以进行增删改查的前提是:等待会话0释放写锁
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,
在执行更新操作(DML)前,会自动给涉及的表加写锁。
所以对MyISAM表进行操作,会有以下情况:
查看哪些表加了锁: show open tables; -- 1代表被加了锁 分析表锁定的严重程度: show status like 'table%'; Table_locks_immediate:即可能获取到的锁数 Table_locks_waited:需要等待的表锁数(如果该值越大,说明存在越大的锁竞争) 建议: Table_locks_immediate/Table_locks_waited > 5000,建议采用InnoDB引擎,否则MyISAM引擎
create table linelock( id int(5) primary key auto_increment, name varchar(20) )engine=innodb; insert into linelock(name) values('1'); insert into linelock(name) values('2'); insert into linelock(name) values('3'); insert into linelock(name) values('4'); insert into linelock(name) values('5');
-- mysql默认自动commiit; oracle 默认不会自动commit
为了研究行锁,暂时将自动commit关闭; set autocommit=0;
以后需要通过commit
会话0:写操作 insert into linelock values('a6'); 会话1:写操作,同样的数据 update linelock set name='ax' where id = 6;
会话0:写操作 insert into linelock values(8,'a8'); 会话1:写操作,不同的数据 update linelock set name='ax' where id=5;
-- 行锁,一次锁一行数据;因此如果操作的是不同数据,则不干扰。
如何仅仅是"查询数据",能否加锁?可以 for update
1. 研究学习时,自动提交关闭: set autocommit=0; start transaction; begin; 2. 通过 for update 对query语句进行加锁。 select * from linelock where id=2 for update;
show index from linelock; alter table linelock add index idx_linelock_name(name); 会话0:写操作 update linelock set name = 'ai' where name = '3'; 会话1:写操作,不同的数据 update linelock set name = 'aiX' where name = '4'; 会话0:写操作 update linelock set name = 'ai' where name = 3; 会话1:写操作,不同的数据 update linelock set name = 'aiX' where name = 4;
-- 可以发现,数据被阻塞了(加锁)
-- 原因:如果索引类发生了类型转换,则索引失效。因此,此次操作,会从行锁转为表锁。
update linelock set name='x' where id > 1 and id < 9; --即在此where范围中,没有id=7的数据
InnoDB默认采用行锁;
缺点:比表锁性能损耗大。
优点:并发能力强,效率高。
因此建议,高并发用InnoDB,否则用MyISAM.
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:等待次数。
事务1和事务2互相持有对方需要的锁而不释放,造成死锁的情况
事务1 开启事务 update tb_account set name='zs' where id=4; update tb_account set name='zs' where id=5; 提交事务 事务2 开启事务 update tb_account set name='zs' where id=5; update tb_account set name='zs' where id=4; 提交事务 当 事务1 update4 的时候,事务2 update5,但是由于innodb是行锁,没有问题,可以执行 继续:事务1 update5 的时候,由于5被事务2锁着,没有释放,会被阻塞,等待事务2提交 然后事务2继续update4,报错:4被事务1锁着呢,同时事务1要操作5,但是5被事务2锁着呢,就遭成了死锁