mysql--锁
mysql不同的存储引擎支持不同的锁机制,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,默认情况下是采用行级锁
MySQL这3种锁的特性可大致归纳如下。 开销、加锁速度、死锁、粒度、并发性能- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
一、MyISAM表锁(MyISAM存储引擎只支持表锁)
1. 命令:show status like 'table%'; 用来分析系统上的表锁定争夺情况,如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况
2. 表级锁的锁模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)
MyISAM表的读操作与写操作之间,以及写操作之间是串行的
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁。想要显式加锁使用:
Lock tables 表名 read local, 表名 read local;
ps:(1).在LOCK TABLES时加了“local”选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾并发插入记录 MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。 当concurrent_insert设置为0时,不允许并发插入。 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。 (2).在用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及到表的锁,并且MySQL不支持锁升级。也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。二、InnoDB锁
与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁
1. 命令:show status like 'innodb_row_lock%';用来分析系统上的行锁的争夺情况,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,说明存在着较严重的锁争用情况
2. 行锁模式:
- 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
- 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁
- 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
请求锁模式 是否兼容 当前锁模式 | X | IX | S | IS |
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
3. 实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
InnoDB存储引擎中不同SQL在不同隔离级别下锁比较隔离级别 一致性读和锁 SQL | Read Uncommited | Read Commited | Repeatable Read | Serializable | |
SQL | 条件 | ||||
select | 相等 | None locks |
Consisten read/ None lock |
Consisten read/ None lock |
Share locks |
范围 | None locks |
Consisten read/ None lock |
Consisten read/ None lock |
Share Next-Key | |
update | 相等 | exclusive locks | exclusive locks | exclusive locks | Exclusive locks |
范围 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
Insert | N/A | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
replace | 无键冲突 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
键冲突 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
delete | 相等 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
范围 | exclusive next-key | exclusive next-key | exclusive next-key | exclusive next-key | |
Select ... from ... Lock in share mode | 相等 | Share locks | Share locks | Share locks | Share locks |
范围 | Share locks | Share locks | Share Next-Key | Share Next-Key | |
Select * from ... For update | 相等 | exclusive locks | exclusive locks | exclusive locks | exclusive locks |
范围 | exclusive locks | Share locks | exclusive next-key | exclusive next-key | |
Insert into ... Select ... (指源表锁) | innodb_locks_unsafe_for_binlog=off | Share Next-Key | Share Next-Key | Share Next-Key | Share Next-Key |
innodb_locks_unsafe_for_binlog=on | None locks |
Consisten read/ None lock |
Consisten read/ None lock |
Share Next-Key | |
create table ... Select ... (指源表锁) | innodb_locks_unsafe_for_binlog=off | Share Next-Key | Share Next-Key | Share Next-Key | Share Next-Key |
innodb_locks_unsafe_for_binlog=on | None locks |
Consisten read/ None lock |
Consisten read/ None lock |
Share Next-Key |
参考文章:
1. MySQL详解--锁