Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the reasons for mysql locking table", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what are the reasons for mysql locking table" this article.

In mysql, the reason for locking a table is that when one program performs the insert, update, or delete operation on the table before commite, and another program performs the same operation on the same table, then an exception with busy resources occurs, that is, locking the table.

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

What are the reasons why mysql locks the table?

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.

In the database, in addition to the contention of traditional computing resources (such as CPU, RAM, Imax O, etc.), data is also a kind of resource shared by many users.

How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access.

From this point of view, locks are particularly important and more complex for databases.

The reason for locking the table

1. The locking table occurs in insert update and delete

2. The principle of locking the table is that the database uses an exclusive locking mechanism. When the above statement is executed, the table is locked until commite or rollback occurs or the database user exits.

First, program An executes insert to tableA, and before program commite, program B also insert tableA. At this time, an exception with busy resources occurs, that is, locking the table.

Second, lock tables often occur in concurrency rather than parallelism (in parallel, when one thread operates on the database, the other thread cannot operate on the database, cpu and iUnix allocation principles)

3. Reduce the probability of locking the table:

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

Specific point: batch execution is changed to single execution, optimizing the non-execution speed of sql itself. If an exception occurs, roll back things.

Examples are as follows:

Use update

Suppose kid is an index field of the table 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; # locks the table

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

Update table set name='feie' where kid=1; # will not lock

Summary: when modifying with the index field as a condition, whether the table lock depends on whether the index field can determine that the record is unique, when the index value corresponding to the record is not unique, the table will be locked, on the contrary, the row lock.

Use delete

If there are two delete: 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 delete 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 delete will lock the table

The above is all the contents of the article "what are the reasons for locking the mysql table?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.