InnoDB implementiert zwei Arten von Standard-Zeilensperren:

Wenn die Transaktion T1 eine Shared-Sperre (S) auf dem Tupel t hält, dann

Wenn eine Transaktion T1 eine exklusive (X) Sperre auf Tupel t hält, kann einer Forderung einer anderen Transaktion T2 nach einer Sperre egal welchen Typs auf t nicht sofort stattgegeben werden. Transaktion T2 muss warten, bis Transaktion T1 ihre Sperre auf Tupel t wieder freigegeben hat.

Außerdem unterstützt InnoDB Multigranuläre Sperren: So können Zeilen und Tabellensperren koexistieren. Um Sperren auf mehreren Granularitätsebenen in die Praxis umzusetzen, gibt es die so genannten Intention Locks. Mithilfe dieser intendierten Sperren kann eine Transaktion anzeigen, welchen Sperrentyp (Shared oder exklusiv) sie später auf einer Zeile einer Tabelle benötigt. In InnoDB werden zwei Arten von Intention Locks verwendet (wir gehen davon aus, dass Transaktion T eine Sperre des angegebenen Typs auf Tabelle R) angefordert hat:

Das Protokoll für Intention Locking ist wie folgt:

Diese Regeln lassen sich gut in einer Matrix der Sperrtypenkompatibilität zusammenfassen:

Der Sperranforderung einer Transaktion wird stattgegeben, wenn die Sperre mit den bereits vorhandenen Sperren kompatibel ist. Ihr wird nicht stattgegeben, wenn die Sperre mit den vorhandenen in Konflikt treten würde. Dann muss die Transaktion abwarten, bis die andere Sperre, die den Konflikt verursachen würde, freigegeben wurde. Wenn eine Sperranforderung mit einer vorhandenen Sperre in Konflikt tritt und nicht gewährt werden kann, weil sie einen Deadlock verursachen würde, wird ein Fehler ausgelöst.

Somit können Intention Locks nichts blockieren, außer vielleicht Sperranforderungen für ganze Tabellen (beispielsweise LOCK TABLES … WRITE). IX und IS sollen vor allem anzeigen, dass jemand eine Tabellenzeile sperrt oder sperren wird.

Das folgende Beispiel zeigt, wie ein Fehler ausgelöst wird, wenn eine Sperranforderung einen Deadlock zur Folge haben würde. An dem Beispiel sind zwei Clients beteiligt, nämlich A und B.

Zuerst legt Client A eine Tabelle mit einer Zeile an und beginnt dann eine Transaktion. Inmitten der Transaktion erwirbt A eine S-Sperre auf der Zeile, indem er im Shared-Modus ein Select auf ihr ausführt:

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)

Dann beginnt Client B eine Transaktion und versucht, die Zeile aus der Tabelle zu löschen:

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

mysql> DELETE FROM t WHERE i = 1;

Die Löschoperation macht eine X-Sperre erforderlich. Diese kann jedoch nicht erteilt werden, da sie mit der S-Sperre von Client A inkompatibel ist. So wird der Request in die Schlange der Sperranforderungen auf diese Zeile gestellt und Client B wird blockiert.

Endlich versucht Client A ebenfalls, die Zeile aus der Tabelle zu löschen:

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

Hier tritt der Deadlock auf, da Client A eine X-Sperre benötigt, um die Zeile sperren zu können. Diese Sperre kann jedoch nicht gewährt werden, da Client B schon vorher eine X-Sperre angefordert hatte, und nur noch darauf wartet, dass A seine S-Sperre freigibt. Doch auch die S-Sperre von A kann nicht auf eine X-Sperre hochgesetzt werden, da B schon vorher eine X-Sperre angefordert hatte. Infolgedessen meldet InnoDB dem Client A einen Fehler und gibt dessen Sperre frei. Jetzt kann der Sperranforderung von Client B stattgegeben werden und B löscht die Zeile aus der Tabelle.