Determine whether MySQL update will lock the table through examples

Two cases:

1. With index 2. Without index

Prerequisites:

Method: Use command line to simulate

1. Since MySQL automatically commits transactions by default, you must first check whether your current database has automatically committed transactions.

Command: select @@autocommit;

The results are as follows:

+————–+
| @@autocommit |
+————–+
| 0 |
+————–+

If it is 1, run the command: set autocommit = 0; set to disable automatic commit

2. The current database table format is as follows

tb_user | CREATE TABLE tb_user (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(32) DEFAULT NULL,
phone varchar(11) DEFAULT NULL,
operator varchar(32) DEFAULT NULL,
gmt_create datetime DEFAULT NULL,
gmt_modified datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Obviously, I didn't add any indexes except the primary key.

Practical example:

1. No Index

Run the command: begin; to start the transaction, and then run the command: update tb_user set phone=11 where name=”c1”; to make the changes. Do not commit the transaction yet.

Open another window and run the command directly: update tb_user set phone=22 where name=”c2”; You will find that the command is stuck. However, when the previous transaction is submitted through commit, the command will run normally and end, indicating that the table is locked.

2. Add an index to the name field

create index index_name on tb_user(name);

Then continue the operation as in step 1, that is, open a transaction and run update tb_user set phone=11 where name=”c1”; do not commit yet

Then run another command update tb_user set phone=22 where name=”c2”; and you will find that the command will not get stuck, indicating that the table is not locked.

But if another update tb_user set phone=22 where name=”c1”; updates the same row, it means the row is locked.

3. Summary

If there is no index, the update will lock the table. If an index is added, the row will be locked.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.