Will Update in a Mysql transaction lock the table?

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, to 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.

This is the end of this article about whether Update in MySQL transaction will lock the table. For more information about MySQL transaction Update locking table, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!