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)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

请求锁模式    是否兼容 当前锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

 

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

　　共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

　　排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

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详解－－锁