The interviewer asked me if I knew MySQL locks and impressed him for the next 15 minutes

As we know, data is also a resource that is shared and accessible by many users. How to ensure the consistency and validity of concurrent data access is a problem that must be solved by all databases. Lock conflict is also an important factor that affects the performance of concurrent data access. From this point of view, locks are particularly important for databases. This article will take you through the various features of Mysql locks.

Table locks

Table level locks are the largest type of mysql locks, indicating that the current operation locks the entire table. The resource cost is lower than that of row locks, and deadlocks do not occur, but lock conflicts are likely to occur.

The biggest feature of this locking mechanism is that the implementation logic is very simple, and the system has the least negative impact.

So locks are acquired and released quickly. Because table-level locking locks the entire table at once, it’s a good way to avoid the deadlocks that plague us.

Table locking is supported by most mysql engines, MyISAM and InnoDB both support table locking.

MyISAM only supports table locks, so performance is relatively low compared to Innodb. Innodb also supports table locks, but row locks are the default, and row locks are used only when queries or other SQL statements pass indexes.

Row locks

Row lock is the smallest type of mysql lock. Because of its small granularity, the probability of resource contention is minimal and the concurrency performance is maximum. However, it also causes deadlock, and the cost of locking and releasing locks increases. Row locks are currently used mainly by Innodb, which is the default storage engine for mysql since 5.5.5.

Row locks are also shared (S or read) and exclusive (X or write) depending on how they are used

Shared lock (S lock, read lock)

If transaction A locks data object 1 with S lock, transaction A can read data object 1 but cannot modify it. Other transactions can only lock data object 1 with S lock but cannot lock data object 1 with X lock until transaction A releases S lock on data object 1. This ensures that other transactions can read data object 1, but no changes can be made to data object 1 until transaction A releases the S lock on data object 1.

Usage:

select ... lock in share mode; ---- Shared lock means that multiple transactions can share a lock for the same data. All transactions can access the data, but can only read the data but cannot modify it.Copy the code

Exclusive lock (X lock, write lock)

If transaction A locks data object 1 with X, transaction A can read data object 1 and modify data object 1. Other transactions cannot lock data object 1 again until transaction A releases the lock on data object 1. This ensures that no other transaction can read or modify data object 1 until transaction A releases the lock on it.

select ... For update ---- Exclusive locks do not coexist with other locks. If a transaction acquires an exclusive lock on a row, other transactions cannot acquire another lock on that rowCopy the code

Intentional Shared Lock (IS) and Intentional Exclusive Lock (IX)

Definition:

Intent shared lock (IS) : a transaction that wants to acquire a shared lock on some records in a table must first place an intent shared lock on the table. Intent mutex (IX) : A transaction that wants to acquire a mutex for certain records in a table must first add an intent mutex to the table.Copy the code

Intentional shared lock and intentional exclusive lock are collectively called intentional lock. Intent locks are introduced to support Innodb’s support for multi-granularity locks.

First, intent locks are table level locks.

Reason: When we need to add a table lock, we need to determine whether the table is locked according to the intent lock, to determine whether it can be successfully added. If the intent lock is a row lock, then we have to traverse all rows in the table to determine. If the intent lock is a table lock, then we can directly determine whether any rows in the table are locked. So setting intent locks at the table level gives much better performance than row locks.

With the intent lock, the database automatically gives transaction A the intent exclusive lock of the request before applying for the row lock (write lock) in the previous example. Transaction B fails when it attempts to lock the write lock on the request, because the write lock on the request will be blocked after the table is intentionally exclusive.

So, the intent lock is:

When a transaction needs to acquire a lock on a resource that is already occupied by an exclusive lock, the database automatically grants the transaction an intent lock on the table. If you need a shared lock, apply for an intended shared lock. If an exclusive lock is required for a row (or rows), apply for an intentional exclusive lock.

Optimistic locking

Optimistic locking is not database inherent, we need to achieve our own. Optimistic locking refers to the operation of the database (update operation), the idea is optimistic, the operation will not cause conflict, the operation of the data, without any other special processing (that is, no lock), and after the update, to determine whether there is a conflict.

The usual implementation is to add a version field to the table as the data in the table is manipulated (updated), and increment the version number of that record by 1 with each operation. That is, query the record first and obtain the version field. If you want to operate (update) the record, check whether the version value at the moment is equal to the version value just queried. If the value is equal, it indicates that there is no other program to operate on it during this period, so you can perform the update. Increment the value of the Version field by 1; If the version value is not the same as the version value just obtained during the update, it indicates that another program has been operating on the version during the update period. In this case, the update operation is not performed.

Example:

1. SELECT data AS old_data, version AS old_version FROM... ; 2. Perform service operations based on the obtained data. UPDATE SET data = new_data, Version = new_version WHERE version = old_version if (updated row > 0) {else {// Failed to obtain optimistic lock, rollback and retry}Copy the code

Advantages: As you can see from the above example, the optimistic locking mechanism avoids the overhead of database locking in long transactions and greatly improves the overall system performance under large concurrent transactions. Disadvantages: Optimistic locking mechanism is usually based on the data storage logic in the system, so it has some limitations. For example, because optimistic locking mechanism is implemented in our system, the update operation from the external system is not controlled by our system, so dirty data may be updated to the database. In the system design stage, the possibility of these situations should be fully considered and corresponding adjustments should be made (for example, the optimistic locking policy should be implemented in the database stored procedure, and only the data update path based on the stored procedure should be open to the outside world, rather than the database table directly to the outside world). Conclusion: Use optimistic lock for reading and pessimistic lock for writing.

Pessimistic locking

Pessimistic locking: Pessimistic locking, as its name suggests, refers to the conservative attitude towards data being modified by outsiders (including other current transactions on the system, as well as transactions from external systems), and therefore locks data for the entire data processing process. The realization of pessimistic lock, often rely on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can truly ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in the system, it can not guarantee that the external system will not modify the data)

To implement pessimistic locks, we must first use set autocommit=0; Disable the mysql autoCommit attribute. Because we need to lock the data after we query it.

After turning off auto commit, we need to manually start transactions.

//1. Start transaction begin; Or start the transaction. Select status from T_goods where id=1 for update; Insert into T_orders (id,goods_id) values (null,1); Update t_goods set status=2; //4. Commit transaction commit; The transaction is committed after executionCopy the code

The implementation the pessimistic locking, pessimistic lock is A pessimist, it think we manipulate data in the transaction A 1, there must be A transaction 1 B to modify the data, so we will directly after data query out in step 2 and exclusive lock (X) lock, prevent other affairs to change the transaction 1, until after we commit, just released the exclusive lock.

Advantages: Ensure the security of data processing. Disadvantages: Locking increases overhead and increases the chance of deadlocks. Concurrency is reduced.

There is a risk that optimistic lock updates will fail, even several times. So if the write is in the majority, the throughput requirements are not high, can use pessimistic lock.

The following three types of locks are all innoDB row locks. As mentioned earlier, row locks are implemented based on indexes. Once a lock operation is not performed on an index, it degrades to a table lock.

Gap lock (next-key lock)

A gap lock, applied to a non-unique index, is intended to prevent other transactions from inserting data into the interval, resulting in “unrepeatable reads”.

If the isolation level of a transaction is downgraded to Read Committed (RC), gap locks automatically fail.

As shown in figure 1 :(1,4), (4,7), (7,11), (11, ∞) are the positions to be locked by the gap lock.

For example:

SELECT * FROM table WHERE id = 8 FOR UPDATE; SELECT * FROM table WHERE id BETWEN 2 AND 5 FOR UPDATE; ---- at this point, (1,4) and (4,7) will be lockedCopy the code

Record locks

Record lock, which blocks index records and acts on unique indexes, as shown in the following figure:

select * from t where id=4 for update; It locks the index record with id=4 to prevent other transactions from inserting, updating, or deleting the row with ID =1. Select * from t where id=4; SnapShot Read is not locked and does not affect other transactions on the data.Copy the code

Key in the lock

A nonunique key lock is a combination of a record lock and a gap lock, as shown below:

Its blocking range includes both the index record and the interval before the index, namely (negative infinity,1], (2,4], (5,7], (8,11], (12, infinity].

Execute in transaction A:

UPDATE table SET name = 'javaHuang' WHERE age = 4; SELECT * FROM table WHERE age = 4 FOR UPDATE; Both statements lock the ranges (2,4], (4,7). That is, InnoDB acquires a temporary key lock for the row and a gap lock for the next interval of the row.Copy the code

In order to solve the illusion problem innoDB in RR isolation level, temporary key lock is introduced.

If the isolation level of the transaction is downgraded to RC, the temporary key lock will also fail.

A deadlock

A deadlock is a situation in which two or more transactions are waiting for each other during execution because they compete for lock resources

This is the normal scenario for deadlocks.

So how do you solve deadlocks?

1. Wait until the transaction times out and roll back the transaction.

2. Perform deadlock checking to proactively roll back a transaction so that another transaction can continue.

Here is a way to resolve the deadlock state:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; View the transactions being lockedCopy the code

Kill trx_mysql_thread_id; -- (trx_mysql_thread_ID)Copy the code

Deadlocks are a very complicated topic, so I’ll just cut it short here, and I’ll write a follow-up article on deadlocks.

conclusion

Through this article, I have roughly understood the functions, functions, implementation and solutions of most of the mysql locks. I want to be a Java development engineer, knowing this level should be enough. After all, we are not DBAs, otherwise it would not be good to know too much and rob the DBA’s job.

Rage for a wave of praise

We believe that hard work will eventually lead us to the life we want.

I will try to update the original dry goods, but also collect some high-quality articles for our daily study. Anyway, if you think you can learn something from me, you can shamelessly ask for a compliment, attention and share here. I will certainly live up to you and add more wonderful articles for your way of learning.

It is not easy to create and persist. Your support is my biggest motivation. Thank you again.