Common pitfalls of MySQL lock

What we share today is a common misconception about MySQL lock. The MySQL lock includes two types of lock and latch. Latch object-oriented is a thread, mainly used to manage the database critical resources of the concurrent access, the lock time is very short, and does not produce deadlocks. No manual intervention is required, so here we are no longer introducing. Lock is a transaction-oriented object that operates on tables, pages, and rows of the database, which manages the access of concurrent threads to shared resources, resulting in deadlocks. Because we now use the InnoDB storage engine for our database. So today is mainly to introduce to you is the InnoDB lock of the common several misunderstandings.

Before we introduce, we need to understand several concepts of lock:

row lock : InnoDB implements a multi-granularity lock, the object is table lock, the action object is a row (Record) is a row lock. The row locks include shared row locks and exclusive row locks.

Shared row lock (S): Allows a transaction to read a row of data.

Exclusive row lock (X): Allows a transaction to delete or update a row of data.

Intent Lock : The database needs to be locked for fine-grained objects, and the coarse-grained objects need to be locked first. Locks on coarse-grained objects become intent locks. InnoDB's intent locks include shared intent table locks and exclusive intent table locks.

shared Intent Table Lock (IS): s lock corresponds to IS lock

Exclusive Intent Table lock (IX): X lock corresponds to IS lock

Lock-compatible : Compatibility refers to allowing the same or different types of locks to exist on the same object.

The following are the compatibility scenarios between the locks:

Well, with the accumulation of knowledge above, here are some common misconceptions to explain:

Myth One: Select Col1,col2 from table1 where col1= ' xxx ' or select count (*) from table1; lock table;

In fact, such a SELECT statement does not lock the resources that are accessed. Because such a query would use a consistent non-locking read, it accesses the image of the resource (the technique used here is MVCC-Multi version concurrency control), so it does not clog other transactions and is not blocked by other transactions (interested students can be verified by experiments, If you do not know the method of the experiment, you can direct me. This is, of course, just a general SELECT statement. If a statement of this format is the following, the access resource is still locked:

Select Col1,col2 from table1 for update; (plus x Lock)

Select Col1,col2 from table1 lock in share mode; (plus S lock)

What is a shared lock (S) is a mutex (X), which is described above. We need to note that S-lock and S-lock are compatible, S-Lock and X-Lock, X-Lock, and X-Lock are incompatible. The compatibility referred to here refers to the compatibility of different transactions with access to the same row (row) resource. A select request, which is explicitly locked, blocks other requests by blocking the intent lock request on the table by other resources. Therefore, in general, if there is no special requirement, the application is not allowed to display lock on the SELECT statement.

Myth Two: Update table1 set col1= ' xxx ' where col2= ' xxx ' do not go index to the performance of the database has no much impact;

When an UPDATE statement is used, the statement first adds an intent lock (IX) to the table it accesses, and if the UPDATE statement goes through the index, it uses row locks (X) to lock only the access records and gaps (to learn more about the algorithm that can be used by Baidu's MySQL lock). If it does not go through the index, it will perform a full table sweep, which will give the entire table an exclusive lock (X). This is the SQL that will block all requests for the table plus intent lock (intent to read and write intent), thus blocking other requests.

Myth Three: The self-growth key will lock the self-growth value during the whole transaction process;

Now the primary key of the table in our database is set to self-growth. Many colleagues think that this will not greatly affect the efficiency of database insertion. In fact, the use of self-growth value does affect the efficiency of data warehousing, when MySQL 5.1.22, the database of self-growth design has been greatly optimized, performance has been greatly improved. In previous versions of 5.1.22, the auto_inc locking was used to generate the self-growing primary key. It does not lock self-growth resources throughout the transaction, but frees resources when the SQL that is to produce the primary key finishes executing. This is why we encounter the fact that the self-growth value is still increasing after the transaction is rolled back. 5.1.22 and later, a lightweight mutex (mutex) was used to achieve self-growth and to control the self-growing pattern through inodb_atuoinc_lock_mode. The database default parameter value is 1, in general, the mutex is used to control the self-growth, only when the bulk inserts will use Auto_inc locking mode.

Myth Four: The use of foreign keys to strengthen constraints, will not affect performance;

Many colleagues in the design of the table structure like the use of foreign keys, here will explain why we do not recommend the use of foreign keys.

If a foreign key is used, the parent table is retrieved when the child table needs to update or insert data. The problem is that the retrieval of the parent table is not using a consistent, non-locking read, but a consistent lock-read using the.

The internal search will be in the following format: SELECT * from parent where ... lock in share mode; Such a retrieval will block requests for other transactions that also access the parent table, affecting performance.

Common pitfalls of MySQL lock