In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article shows you how to understand MySQL 5.5 InnoDB table locks, which is concise and easy to understand. I hope you can learn something through the detailed introduction of this article.
For tables without indexes, MySQL uses table-level locks, write operations do not block read operations, and read operations do not block write operations; the write operation of one session locks the entire table, and other sessions need to wait until the session commits or rolls back the transaction.
Session ①
Mysql > create table T12 (id tinyint (3) unsigned not null)
-> name varchar (10) not null)
-> engine=innodb auto_increment=8 default charset=gbk
Query OK, 0 rows affected (0.12 sec)
Mysql > show keys from T12
Empty set (0.00 sec)
Mysql > show variables like'% commit%'
+-+ +
| | Variable_name | Value |
+-+ +
| | autocommit | ON |
| | innodb_commit_concurrency | 0 | |
| | innodb_flush_log_at_trx_commit | 1 | |
+-+ +
3 rows in set (0.00 sec)
Mysql > set autocommit=0
Query OK, 0 rows affected (0.00 sec)
Mysql > show variables like'% commit%'
+-+ +
| | Variable_name | Value |
+-+ +
| | autocommit | OFF |
| | innodb_commit_concurrency | 0 | |
| | innodb_flush_log_at_trx_commit | 1 | |
+-+ +
3 rows in set (0.00 sec)
Mysql > insert into T12 values (10m.neo')
Query OK, 1 row affected (0.00 sec)
Mysql > commit
Query OK, 0 rows affected (0.00 sec)
Mysql > select * from T12
+-+ +
| | id | name |
+-+ +
| | 10 | Neo |
+-+ +
1 row in set (0.00 sec)
Session ②
Mysql > show variables like'% commit%'
+-+ +
| | Variable_name | Value |
+-+ +
| | autocommit | ON |
| | innodb_commit_concurrency | 0 | |
| | innodb_flush_log_at_trx_commit | 1 | |
+-+ +
3 rows in set (0.00 sec)
Mysql > set autocommit=0
Query OK, 0 rows affected (0.00 sec)
Mysql > show variables like'% commit%'
+-+ +
| | Variable_name | Value |
+-+ +
| | autocommit | OFF |
| | innodb_commit_concurrency | 0 | |
| | innodb_flush_log_at_trx_commit | 1 | |
+-+ +
3 rows in set (0.00 sec)
Mysql > begin
Query OK, 0 rows affected (0.00 sec)
Mysql > select * from T12
+-+ +
| | id | name |
+-+ +
| | 10 | Neo |
+-+ +
1 row in set (0.00 sec)
Session ①
Mysql > update T12 set name='trinity' where id=10
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Session ②
The following DML statement blocks all the time
Mysql > insert into T12 values (20m)
There will be a timeout prompt after a period of time.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Session ①
Mysql > rollback
Query OK, 0 rows affected (0.00 sec)
Session ②
Mysql > insert into T12 values (20m)
Query OK, 1 row affected (0.00 sec)
Mysql > commit
Query OK, 0 rows affected (0.01 sec)
For indexed tables, write operations do not block read operations, and read operations do not block write operations; if MySQL uses index scanning during write operations, row-level locks are used, and the write operation of one session will lock the modified rows, other sessions will have to wait until the session commits or rolls back the transaction, and other sessions will not affect the write operations of other rows, and row locks will block table locks If MySQL uses full table scans, table-level locks are used, and write operations of one session lock the entire table, while other sessions need to wait until the session commits or rolls back the transaction, and the table lock blocks row locks.
Session ①
Mysql > create index idx_t12_id on T12 (id)
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
View Index
Mysql > show keys from T12
+ -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+ -+
| | T12 | 1 | idx_t12_id | 1 | id | A | 3 | NULL | NULL | | BTREE |
+ -+
1 row in set (0.00 sec)
View the execution plan
Mysql > explain select * from T12 where id=20
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | T12 | ALL | idx_t12_id | NULL | NULL | NULL | 1 | Using where |
+-- +
Mysql > delete from T12 where id=20
Query OK, 2 rows affected (0.00 sec)
Mysql > select * from T12
+-+ +
| | id | name |
+-+ +
| | 10 | Neo |
+-+ +
1 row in set (0.00 sec)
Session ②
View the execution plan
Mysql > explain select * from T12 where id=10
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | T12 | ref | idx_t12_id | idx_t12_id | 1 | const | 1 | |
+-- +
1 row in set (0.00 sec)
Mysql > update T12 set name='Jack' where id=10
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql > select * from T12
+-+ +
| | id | name |
+-+ +
| | 10 | Jack |
| | 20 | Trinity |
| | 20 | Trinity |
+-+ +
3 rows in set (0.00 sec)
Mysql > rollback
Query OK, 0 rows affected (0.01 sec)
Mysql > show processlist
+-+ -+
| | Id | User | Host | db | Command | Time | State | Info |
+-+ -+
| | 1 | system user | | NULL | Connect | 769140 | Slave has read all relay log; waiting for the slave thread to update it O thread to update it | NULL |
| | 2 | system user | | NULL | Connect | 769141 | Connecting to master | NULL |
| | 13 | event_scheduler | localhost | NULL | Daemon | 621090 | Waiting on empty queue | NULL | |
| | 76 | neo | localhost | fire | Sleep | 180 | | NULL |
| | 78 | neo | localhost | fire | Query | 0 | NULL | show processlist | |
+-+ -+
5 rows in set (0.00 sec)
Mysql > explain select * from T12
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | T12 | ALL | NULL | NULL | NULL | NULL | 1 |
+-- +
1 row in set (0.00 sec)
The following update uses a full table scan instead of an index, which adds a table-level lock and is blocked.
Mysql > update T12 set name='Jack'
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Session ①
Mysql > rollback
Query OK, 0 rows affected (0.05 sec)
Session ②
With the rollback of the session ①, the session ② executes successfully
Mysql > update T12 set name='Jack'
Query OK, 3 rows affected (12.41 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Mysql > rollback
Query OK, 0 rows affected (0.00 sec)
Session ①
Mysql > update T12 set name='Jack'
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Session ②
The insert operation will always be blocked.
Mysql > insert into T12 values (30m Lily`)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Session ①
Mysql > rollback
Query OK, 0 rows affected (0.00 sec)
Session ②
Mysql > insert into T12 values (30m Lily`)
Query OK, 1 row affected (0.09 sec)
Two rows of data use the same index, and locking on two different rows will also cause lock waiting.
Mysql > show create table tab_with_index\ G
* * 1. Row *
Table: tab_with_index
Create Table: CREATE TABLE `tab_with_ index` (
`id`int (11) DEFAULT NULL
`name` varchar (10) DEFAULT NULL
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (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)
Mysql > show keys from tab_with_index
+ -- +
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+ -- +
| | tab_with_index | 1 | id | 1 | id | A | 7 | NULL | NULL | YES | BTREE |
+ -- +
1 row in set (0.00 sec)
Session ①
Query using where id=1 and name='1' condition
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)
Session ②
Query the where id=1 and name='4' condition. Because the same index is used as the session ①, even if different fields are queried, it will cause lock waiting.
Mysql > select * from tab_with_index where id=1 and name='4' for update
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
In this case, you can consider creating a federated index
Session ①
Mysql > create index idx_id_name on tab_with_index (id,name)
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > select * from tab_with_index where id=1 and name='4' for update
+-+ +
| | id | name |
+-+ +
| | 1 | 4 |
+-+ +
1 row in set (0.00 sec)
Session ②
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)
Blocking examples of tables using different indexes in the InnoDB storage engine
Session ①
Mysql > show create table tab_with_index\ G
* * 1. Row *
Table: tab_with_index
Create Table: CREATE TABLE `tab_with_ index` (
`id`int (11) DEFAULT NULL
`name` varchar (10) DEFAULT NULL
KEY `id` (`id`)
KEY `idx_ name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Mysql > desc tab_with_index
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (11) | YES | MUL | NULL |
| | name | varchar (10) | YES | MUL | NULL |
+-+ +
2 rows in set (0.01sec)
Mysql > select * from tab_with_index
+-+ +
| | id | name |
+-+ +
| | 1 | 1 |
| | 2 | 2 |
| | 3 | 3 |
| | 4 | 4 |
| | 1 | 4 |
+-+ +
5 rows in set (0.00 sec)
Mysql > explain select * from tab_with_index where id=1 for update
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | tab_with_index | ref | id | id | 5 | const | 2 | NULL |
+-- +
1 row in set (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)
Mysql > select * from tab_with_index where id=1 for update
+-+ +
| | id | name |
+-+ +
| | 1 | 1 |
| | 1 | 4 |
+-+ +
2 rows in set (0.01sec)
Session ②
Mysql > explain select * from tab_with_index where name='4' for update
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | tab_with_index | ref | idx_name | idx_name | 13 | const | 2 | Using index condition |
+-+-
1 row in set (0.00 sec)
Mysql > select * from tab_with_index where name='4' for update
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
The above is how to understand MySQL 5.5 InnoDB watch locks. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
