Will update in mysql lock the table?

There are two situations in which update in mysql will lock the table: 1. When update does not have an index, the transaction before the statement is submitted through commit, and the command will run normally and end, then update will lock the table; 2. Update adds When indexing, the command will not get stuck and the table will not be locked, but the same row will be updated and the row will be locked.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

Will update in mysql lock the table?

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

When the previous transaction passes commit Once submitted, the command will run normally and end, indicating that the table is locked.

Two situations:

• 1, with index

• 2, without index

Premise introduction:

Method: Use the command line to simulate

1.mysq Since automatic transaction submission is turned on by default, you must first check whether your current database is turned on Automatically commit transactions.

Command: select @@autocommit;

The result is as follows:

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

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, except for the primary key, I did not add any index

Actual 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 modify, do not commit the transaction first.

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, but when the previous transaction is submitted through commit, the command will run normally. It ends, 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 1, which is an open transaction and run update tb_user set phone=11 where name= "c1"; Don't submit it first

Then run update tb_user set phone=22 where name="c2";It is found that the command will not get stuck, indicating that there is no lock table

But if another One is also update tb_user set phone=22 where name="c1"; update the same row, indicating that the row is locked

3. Summary

If not Index, so update will lock the table. If an index is added, the row will be locked.

Recommended learning: mysql video tutorial

The above is the detailed content of Will update in mysql lock the table?. For more information, please follow other related articles on the PHP Chinese website!