Mysql INNODB Engine lock principle test, mysqlinnodb engine lock
How does mysql INNODB Engine lock work? Let's test it.
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, '20160301', '20160301 ');
Insert into test VALUES (2, '20160301', '20160301 ');
Insert into test VALUES (3, '20160301', '20160301 ');
Insert into test VALUES (4, '20160301', '20160301 ');
Insert into test VALUES (5, '20160301', '20160301 ');
Insert into test VALUES (6, '20160301', '20160301 ');
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)
Set autocommit = off to complete the experiment in the CMD window;
1. modify a different record without a primary key
Session1:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session2:
Mysql> update test set B = '000000' where a = 2; -- First hang, an error is reported after a period of time.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2. If no primary key exists, add one data entry and then modify another data entry.
Session1:
Mysql> insert into test values (7, '20160301', '20160301 ');
Query OK, 1 row affected (0.00 sec)
Session2:
Mysql> update test set B = '000000' where a = 2; -- First hang, an error is reported after a period of time.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
3. Modify different records with primary keys.
Alter table test add primary key ();
No full table lock is generated when a primary key exists.
Session1:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session2:
Mysql> update test set B = '000000' where a = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
If the same record is modified when a primary key exists, it will be hang, indicating that it is a row lock.
Session1:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session2:
Mysql> update test set B = '000000' where a = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
4. insert and update with primary keys
Session1:
Mysql> insert into test values (8, '20160301', '20160301 ');
Query OK, 1 row affected (0.00 sec)
Session2:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
5. Modify different records without Indexing
Session1:
Mysql> update test set c = '000000' where B = '000000 ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session2:
Mysql> update test set c = '000000' where B = '000000 ';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
6. Modify different records with indexes
Create index ind_t_ B ON test (B );
Session1:
Mysql> update test set c = '000000' where B = '000000 ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session2:
Mysql> update test set c = '000000' where B = '000000 ';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Conclusion: When an index is used (and I also tested whether the index is useless or the row lock), the row lock is used. Otherwise, the entire table is locked, it is convenient to have no row locks in Oracle.
How can I disable Mysql's Innodb engine?
Mysql supports the Innodb Engine by default in many versions, while Yike CRM uses the MyISAM engine.
To disable the Innodb engine, modify my. cnf (d:/xampp/mysql/bin/my. cnf)
Open the following two options (that is, cancel the annotation and add it if there is no comment ):
Skip-innodb
Default-storage-engine = MyISAM
If this line of skip-innodb parameters exists, delete the previous.
Default-storage-engine = MyISAM. The parameter my. cnf may not exist. You need to add a row.
Save my. cnf and restart mysql.
D: The ib_logfile0, ib_logfile1, and ibdata1 files in the/xampp/mysql/data directory can be deleted.
How to shield Innodb Storage engine in mysql
Add in my. cnf or my. ini
Skip-innodb
