Mysql in the lock

In mysql, if you need to modify a row of data, you will first check whether the row is locked. If not, you will create a lock that contains (transaction ID, wait state) and then associate the lock with the row. And the data that we’re updating has to be loaded into memory so this row of data that needs to be updated and the associated lock structure is in memory.Now transaction B comes in and tries to modify this row and finds that transaction A is already locked, so he has to lock and waitTransaction A is in A false lock state, so it is being modified. If transaction A is finished, it will release its lock, and then check to see if other transactions are also locking the data, and then change transaction B’s lock state to false to allow transaction B to run.

Exclusive locks and shared locks

Above we introduced that if multiple transactions update the same row, only one transaction can have an exclusive lock, and other transactions can also have an exclusive lock and wait later. Now is there a transaction that needs to read this data that needs to wait? The answer is: no. This is when the MVCC mechanism is triggered. That is, we read data and write data are not mutually exclusive, and MVCC is designed to solve this problem. At this point you can read the data completely according to your ReadView to undoLog chain to find a version snapshot to use. This exclusive lock on multiple transaction update data is called an X lock. What if we also want to lock our query? You can add a shared lock which is an S lock. However, S lock and X lock cannot exist at the same time, because we update the data transaction has an exclusive lock, this time you want to query the shared lock. You have to wait. Such as:

select * from User lock in share mode
Copy the code

So if someone else adds a shared lock, you can only wait to update data with an exclusive lock because the lock and lock are mutually exclusive. Of course, when we query the MVCC mechanism is not locked by default.

It can be seen from the table that the shared lock is S lock and S lock are not mutually exclusive. In addition, the query can also add X lock, which is an exclusive lock

select * from users for update
Copy the code

As long as you add an exclusive lock, you can only allow others to manipulate data until the transaction has completed. An exclusive lock is required to update data from multiple transactions so that dirty writes do not occur. A transaction lock is held after one transaction has completed and the next transaction is woken up to update the data.

Table locks

Mysql > alter table lock (s); Mysql > alter table lock (s);

LOCK TABLE name READ (TABLE level shared LOCK) LOCK TABLE citizen WRITE (TABLE level exclusive LOCK)Copy the code

Generally speaking, no one goes to add a watch lock. There are also two cases where table locks are applied.

1. An exclusive lock is added at the row level and an intent exclusive lock is added at the table level if a transaction updates data in the table.
2. If a transaction is performing a query on a table, a table level intent shared lock is added

In fact, we usually operate tables more common locks are table level intention share lock and table level intention exclusive lock, but intention locks are not mutually exclusive.

You can see from the above table that if you add a table exclusive lock, it will be mutually exclusive with the table exclusive lock that is automatically added to the table. No one can update the table. So generally we update the table and add an exclusive lock on the target row, and then we read the data using MVCC multi-version concurrency. It won’t affect how you modify the data.