Mysql InnoDB行ロック実装方式

簡単な説明:InnoDBロー・ロックは、インデックス上のインデックス・アイテムにロックをかけることによって実現されます.この点MySQLは、データ・ブロック内で対応するデータ・ローにロックをかけることによって実現されます.InnoDBというロー・ロックの実装の特徴は、インデックス条件でデータを取得するだけで、InnoDBはロー・レベル・ロックを使用します.そうしないと、InnoDBはテーブル・ロックを使用します.
1.インデックス条件クエリを通過しない場合、InnoDBはロー・ロックではなくテーブル・ロックを使用します.
session_1
session_2

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_no_index where id = 1 ;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_no_index where id = 2 ;
+------+------+
| id   | name |
+------+------+
| 2    | 2    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_no_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_no_index where id = 2 for update;
  ...

見たところsession_1行に排他ロックをかけただけだが、session_2他の行の排他ロックを要求すると、ロック待ちが表示されます!なぜならインデックスがない場合、InnoDBはテーブルロックしか使用できないからです.
 2.表tab_with_indexのidフィールドにはインデックスがあり、nameフィールドにはインデックスがありません

--   tab_with_index 
mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)

mysql> alter table tab_with_index add index id(id);
Query OK, 4 rows affected (0.24 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_with_index where id = 1 ;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_with_index where id = 2 ;
+------+------+
| id   | name |
+------+------+
| 2    | 2    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tab_with_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)
 
mysql> select * from tab_with_index where id = 2 for update;
+------+------+
| id   | name |
+------+------+
| 2    | 2    |
+------+------+
1 row in set (0.00 sec)

MySQLの行ロックはインデックスに対するロックであり、レコードに対するロックではないため、同行しないレコードへのアクセスである.
 
同じインデックスキーを使用している場合は、ロック競合が発生します.デザインを適用するときはこの点に注意してください.

mysql> alter table tab_with_index drop index name;
Query OK, 4 rows affected (0.22 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into tab_with_index  values(1,'4');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tab_with_index where id = 1;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
| 1    | 4    |
+------+------+
2 rows in set (0.00 sec)

-- session_1
-- session_2

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_with_index where id = 1 and name = '1' for update;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
+------+------+
1 row in set (0.00 sec)
 

mysql> select * from tab_with_index where id = 1 and name = '4' for update;
  ... 

session_2訪問したのはセッション_1異なるレコードですが、同じインデックスが使用されているため、ロックを待つ必要があります.