MySQL:数据库锁

分类

锁的分类(oracle)

  1. 按操作划分,可分为DML锁、DDL锁
  2. 按锁的粒度划分,可分为表级锁、行级锁、页级锁(mysql)
  3. 按锁级别划分,可分为共享锁、排他锁
  4. 按加锁方式划分,可分为自动锁、显示锁
  5. 按使用方式划分,可分为乐观锁、悲观锁

DML锁(data locks,数据锁),用于保护数据的完整性,其中包括行级锁(Row Locks (TX锁))、表级锁(table lock(TM锁))。 DDL锁(dictionary locks,数据字典锁),用于保护数据库对象的结构,如表、索引等的结构定义。其中包排他DDL锁(Exclusive DDL lock)、共享DDL锁(Share DDL lock)、可中断解析锁(Breakable parse locks)

  • MyISAM和MEMORY采用表级锁(table-level locking)
  • BDB采用页面锁(page-level locking)或表级锁,默认为页面锁
  • InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

行级锁

行级锁是MySQL中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

  • 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  • 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

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

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。 在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking。

当两个事务同时执行,一个锁住了主键索引,在等待其他相关索引。另一个锁定了非主键索引,在等待主键索引。这样就会发生死锁。

发生死锁后,InnoDB一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。

-- 明确指定主键,并且有此笔资料,row lock
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

-- 明确指定主键,若查无此笔资料,无lock
SELECT * FROM products WHERE id='-1' FOR UPDATE;

-- 无主键,table lock
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

-- 主键不明确,table lock
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

表级锁

表级锁是MySQL中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。BDB支持页级锁