What is the reason why mysql locks the table?

In mysql, the reason for locking the table is that when one program performs the insert, update or delete operation on the table but has not yet committed, another program also performs the same operation on the same table, then it will An exception occurs when the resource is busy, that is, the table is locked.

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

What is the reason for mysql lock table

Lock is a mechanism for computers to coordinate multiple processes or threads to access a certain resource concurrently.

In the database, in addition to the competition for traditional computing resources (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users.

How to ensure the consistency and effectiveness of concurrent access to data is a problem that all databases must solve. Lock conflicts are also an important factor affecting the performance of concurrent access to databases.

From this perspective, locks are particularly important and more complex for the database.

The reason for table locking

1. The locking table occurs during insert update and delete.

2. The principle of table locking is that the database uses exclusive Locking mechanism, when the above statement is executed, the table is locked until a commit occurs or a rollback occurs or the database user exits

First, program A executes the insert of tableA and has not committed yet. Program B also inserts tableA. At this time, a resource busy exception will occur, which is the lock table

. Second, the lock table often occurs in concurrency rather than parallelism (in parallel, when one thread operates the database, another Threads cannot operate the database, CPU and I/O allocation principle)

3. Reduce the probability of locking the table:

Reduce the time between the execution of insert, update, and delete statements to commit.

Specifically, change batch execution to single execution, optimize the non-execution speed of sql itself, and roll back things if exceptions occur

Examples are as follows:

Use update

Assume that kid is an index field of the table and the value is not unique:

1): If kid has multiple records with a value of 12, then:

Update table set name='feie' where kid=12; #Will lock the table

2): If kid has a unique record with a value of 1 then:

='feie' where kid=1; #Will not lock

Summary: When using the index field as a condition for modification, whether the table is locked depends on whether the index field can determine the unique record. When the index value corresponds If the record is not unique, a table lock will be performed, otherwise a row lock will be performed.

Use delete

If there are two deletes: kid1 and kid2 are index fields

1): Statement 1 delete from table where kid1=1 and kid2=2;

　2): Statement 2 delete from table where kid1=1 and kid2=3;

　　 # Such two deletes will not lock the table

　1): Statement 1 delete from table where kid1=1 and kid2=2;

　2): Statement 2 delete from table where kid1=1;

　　　 # Such two deletes will lock the table

Summary: If you perform a deletion operation on the same table, try to make the deletion conditions uniform, otherwise they will affect each other and cause the table to be locked

Recommended learning: mysql video tutorial

The above is the detailed content of What is the reason why mysql locks the table?. For more information, please follow other related articles on the PHP Chinese website!