Mysql InnoDB 锁模型

简单的说，InnoDB在执行读操作时，会加共享锁；执行更新或删除操作时，会加排它锁；执行完之后就会释放所加的锁。但在一个事务中，即便执行完了一条SQL语句，也不会马上释放掉执行这条语句时所加的锁；直到事务提交或回滚，才会释放掉所有事务期间加上去的锁。无论是不是在一个事务中执行读取、更新或删除操作，都会先加锁，然后再操作，只不过是释放锁的时机不同罢了：不在一个事务中，执行完之后，就马上释放掉锁；在一个事务中，则要等到事务提交或回滚的时候才会释放掉所有的锁（当然是当前事务期间加上去的锁了）。

所以事务的使用会使得死锁的几率大大的提升。所以有好多对数据一致性要求不高的互联网应用都不使用事务。而在需要使用事务的应用中应该好好的优化设计系统架构，以避免死锁的产生。

以下是InnoDB锁模型原文。

原文地址：http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html

InnoDB Lock Modes

InnoDB implements standard row-level locking where there are two types of locks:

• A shared (S) lock permits a transaction to read a row.

• An exclusive (X) lock permits a transaction to update or delete a row.

If transaction T1 holds a shared (S) lock on row r, then requests from some distinct transaction T2 for a lock on row r are handled as follows:

• A request by T2 for an S lock can be granted immediately. As a result, both T1 and T2 hold an S lock on r.

• A request by T2 for an X lock cannot be granted immediately.

If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.

Additionally, InnoDB supports multiple granularity locking which permits coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks（意向锁？） are used. Intention locks are table locks in InnoDB. The idea behind intention locks is for a transaction to indicate which type of lock (shared or exclusive) it will require later for a row in that table. There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t):

• Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.

• Intention exclusive (IX): Transaction T intends to set X locks on those rows.

For example, SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IXlock.

The intention locking protocol is as follows:

• Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t.

• Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

These rules can be conveniently summarized by means of the following lock type compatibility matrix.

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

Thus, intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE).The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

The following example illustrates how an error can occur when a lock request would cause a deadlock. The example involves two clients, A and B.

First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an S lock on the row by selecting it in share mode:

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.10 sec)

Next, client B begins a transaction and attempts to delete the row from the table:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

The delete operation requires an X lock. The lock cannot be granted because it is incompatible with the S lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.

Finally, client A also attempts to delete the row from the table:

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result,InnoDB generates an error for one of the clients and releases its locks. The client returns this error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

At that point, the lock request for the other client can be granted and it deletes the row from the table.

有价值的评论：

Rows are locked during updates using indexes. For example, 

update tab set col1=3 where col2=17;

will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked).

Any operation that does a table scan for update/delete will lock all the rows in the table.

...and use

SHOW ENGINE INNODB STATUS

to inspect any deadlock reasons!