What is the principle of MySQL's InnoDB engine lock, to do an experiment.
Mysql> SELECT VERSION ();
+-----------+
| VERSION () |
+-----------+
| 5.5.20 |
+-----------+
1 row in Set (0.00 sec)
CREATE TABLE Test
(
A INT (5),
b VARCHAR (10),
C VARCHAR (10)
);
INSERT into Test VALUES (1, ' 111 ', ' 111 ');
INSERT into Test VALUES (2, ' 222 ', ' 222 ');
INSERT into Test VALUES (3, ' 333 ', ' 333 ');
INSERT into Test VALUES (4, ' 444 ', ' 444 ');
INSERT into Test VALUES (5, ' 555 ', ' 555 ');
INSERT into Test VALUES (6, ' 666 ', ' 666 ');
COMMIT;
Mysql> select * from test;
+------+------+------+
| A | B | C |
+------+------+------+
| 1 | 111 | 111 |
| 2 | 222 | 222 |
| 3 | 333 | 333 |
| 4 | 444 | 444 |
| 5 | 555 | 555 |
| 6 | 666 | 666 |
+------+------+------+
6 rows in Set (0.00 sec)
To complete the experiment in the CMD window, set Autocommit=off is required;
1. In the absence of a primary key, modify a different record
Session1:
mysql> Update test set b= ' 111 ' where a=1;
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
Session2:
mysql> Update test set b= ' 222 ' where a=2;--first hang, error after a period of time
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
2. Without a primary key, add a new piece of data and then modify the other data
Session1:
mysql> INSERT INTO test values (7, ' 777 ', ' 777 ');
Query OK, 1 row Affected (0.00 sec)
Session2:
mysql> Update test set b= ' 222 ' where a=2;--first hang, error after a period of time
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
3. Modify a different record in the case of a primary key
ALTER TABLE Test ADD PRIMARY KEY (a);
When there is a primary key that does not produce a lock full table
Session1:
mysql> Update test set b= ' 111 ' where a=1;
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
Session2:
mysql> Update test set b= ' 222 ' where a=2;
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
Modify the same record when there is a primary key, hang it, the description is the row lock
Session1:
mysql> Update test set b= ' 111 ' where a=1;
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
Session2:
mysql> Update test set b= ' 111 ' where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
4. In the case of a primary key, insert and update
Session1:
mysql> INSERT INTO test values (8, ' 888 ', ' 888 ');
Query OK, 1 row Affected (0.00 sec)
Session2:
mysql> Update test set b= ' 111 ' where a=1;
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
5. In the absence of an index, modify a different record
Session1:
mysql> Update test set c= ' 111 ' where b= ' 111 ';
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
Session2:
mysql> Update test set c= ' 222 ' where b= ' 222 ';
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
6. In the case of an index, modify a different record
CREATE INDEX ind_t_b on test (b);
Session1:
mysql> Update test set c= ' 111 ' where b= ' 111 ';
Query OK, 0 rows Affected (0.00 sec)
Rows matched:1 changed:0 warnings:0
Session2:
mysql> Update test set c= ' 222 ' where b= ' 222 ';
Query OK, 0 rows affected (0.01 sec)
Rows matched:1 changed:0 warnings:0
Summary: When the index is used (I also tested the index is not used in the case, or row lock), it is a row lock, otherwise lock the table, there is no convenient row lock in Oracle.
The principle test of MySQL's InnoDB engine lock
