MySQL InnoDB 锁表与锁行

由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子: 假设有个表单products ,里面有id跟name二个栏位,id是主键。

例1: (明确指定主键,并且有此笔资料,row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键,若查无此笔资料,无lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键,table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确,table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确,table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注:

1. FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。
2. 要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。

在MySql 5.0中测试确实是这样的

另外:MyAsim 只支持表级锁,InnerDB支持行级锁
添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定,也不被其它事务修改(修改、删除)
是表级锁时,不管是否查询到记录,都会锁定表

此外,如果A与B都对表id进行查询但查询不到记录,则A与B在查询上不会进行row锁,但A与B都会获取排它锁,此时A再插入一条记录的话则会因为B已经有锁而处于等待中,此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction然后释放锁,此时A就获得了锁而插入成功

Innodb中的行锁与表锁

前面提到过,在Innodb引擎中既支持行锁也支持表锁,那么什么时候会锁住整张表,什么时候或只锁住一行呢?

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

在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。

行级锁都是基于索引的,如果一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁。行级锁的缺点是:由于需要请求大量的锁资源,所以速度慢,内存消耗大。

行级锁与死锁

MyISAM中是不会产生死锁的,因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在InnoDB中,锁是逐步获得的,就造成了死锁的可能。

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

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

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

有多种方法可以避免死锁,这里只介绍常见的三种,具体如下

相关推荐