Two locking strategies

Situation: there is a taobao shop, there are 100 eggs for sale, now there are two people, Xiao Ming and Xiao Hong, respectively placed an order of 60 eggs at the same time, ask how many eggs are left in the shop? (ANSWER: -20)

name stock
egg 100 
int buy (a) {
    int stock = db.exec("select stock from goods where name = 'egg'");
    if (stock >= 60) {
        return db.exec("update goods set stock = stock - 60 where name = 'egg'");
    }
    return 0;
}
Copy the code

When Ming and Hong order at the same time, they may both get 100 stock, so they will end up with -20 eggs left.

Pessimistic locking

The intuitive idea is to lock when entering the function and unlock when exiting the function, so that Xiao Ming and Xiao Hong can place orders at the same time, but the buy function will not be executed at the same time.

int buy (a) {
    std::lock_guard<std::mutex> lock(mutex);
    int stock = db.exec("select stock from goods where name = 'egg'");
    if (stock >= 60) {
        return db.exec("update goods set stock = stock - 60 where name = 'egg'");
    }
    return 0;
}
Copy the code

This is also what pessimistic locking means: always believing that the data you are using is unsafe and therefore needs to be locked, while others need to wait for the lock to be released. It’s like sitting in a hole and locking your door, because you always expect someone else to open it.

Optimistic locking

Another idea, which is less intuitive, is to terminate the update if the value of stock changes when you update it.

int buy (a) {
    int stock = db.exec("select stock from goods where name = 'egg'");
    if (stock >= 60) {
        return db.exec("update goods set stock = stock - 60 \ where name = 'egg' and stock = %d", stock);
    }
    return 0;
}
Copy the code

The code above reflects the Compare And Swap lock-free algorithm. The result may be ok for this scenario, but the process may not be.

Imagine that If Hong placed an order and cancelled it immediately, the stock experienced a change of 100-40-100, but Ming would still think that stock had not changed, because the value of stock was 100 both times. This problem is called THE ABA problem.

Tip: ABA is not an abbreviation for something. It means the same thing as one, two, one, 100-40-100.

Optimistic locking means that you always think that the data you are using is relatively secure and not locked, but when updating, you will determine whether others have updated the data. It’s like some people keep a hair in the bottom of their drawer.

You can introduce a version column to solve the ABA problem.

name stock version
egg 100 0 
int buy (a) {
    int stock, version = db.exec("select stock, version from goods where name = 'egg'");
    if (stock >= 60) {
        return db.exec("update goods set stock = stock - 60, version = version + 1 \ where name = 'egg' and version = %d", version);
    }
    return 0;
}
Copy the code

But in fact, there are problems with this situation. One is that the extra column increases the storage cost, and the other is that if the inventory of eggs in this situation is 120, then one person will fail to place an order.

Therefore, we need to introduce a loop in the code to solve the problem of a full inventory order failure, and the introduction of a loop increases the CPU overhead.

int buy (a) {
    int stock, version = db.exec("select stock, version from goods where name = 'egg'");
    int times = 10;
    while (stock >= 60 && times--) {
        int rows = db.exec("update goods set stock = stock - 60, version = version + 1 \ where name = 'egg' and version = %d", version);
        if (rows > 0) return row;
        stock, version = db.exec("select stock, version from goods where name = 'egg'");
    }
    return 0;
}
Copy the code

Three lock properties

Shared lock (S lock)

Shared locks are read locks mentioned above. Shared locks are mutually compatible but mutually exclusive.

select column from table lock inshare mode; # explicit lockCopy the code

At the isolation level of repeatable reads, all select that are not explicitly locked are snapshot reads, and select that are explicitly locked are current reads.

Exclusive lock (X lock)

An exclusive lock is the write lock mentioned above. An exclusive lock is mutually exclusive with any attribute lock.

select column from table forupdate; Update explicitlytable set column = 1 where key = 1; # implicit lockingdelete from table where key = 1; # implicit lockinginsert into table values(1); # implicit lockingCopy the code

At the isolation level of repeatable reads, all select that are not explicitly locked are snapshot reads, and select that are explicitly locked are current reads.

Intent Lock

Thread A locks A row in table T. If Thread B wants to lock table T, it needs to ensure that no table lock exists on table T and no row in table T is locked. Therefore, it needs to check whether each row of table T has a row lock.

Intent lock is to solve the above situation, intent lock is a table level lock that conflicts with table lock but not row lock, page lock. In the above scenario, Thread A only needs to add intentional locks and row locks to table T, and Thread B only needs to determine whether there is an intentional lock on table T.

Intentional lock is divided into intentional shared lock and intentional exclusive lock.

Intent shared lock (IS lock)

Select column from table lock in share mode; Automatically add intent to share lock to the table.

To acquire a shared lock for some rows, a transaction must first acquire an intended shared lock for the table.

Intent exclusive lock (IX lock)

Select column from table for UPDATE; Automatic lock on the table.

To obtain an exclusive lock on some rows, a transaction must first obtain an intentional exclusive lock on the table.

Four types of lock granularity

Library lock

Generally called a global lock, but I prefer to call it a library lock, you can lock an entire database instance and make the entire database read-only.

flush tables withread lock; Unlock tables; Unlock the client after it is disconnectedCopy the code

The library lock is applied for a full logical backup (mysqldump).

Table locks

Table locks are used to lock a single table. There are two types: read locks (shared locks) and write locks (mutex locks).

lock tables tb read(write); Unlock tables; Unlock the client after it is disconnectedCopy the code
Thread A adds the read lock Thread A adds the write lock
Thread A reads data successful successful
Thread A writes data failure successful
Thread B reads data successful blocking
Thread B writes data blocking blocking
Thread A adds the read lock cover cover
Thread A adds the write lock cover cover
Thread B added the read lock successful blocking
Thread B adds a write lock blocking blocking

Row lock (also known as record lock)

Row locks are automatically added as needed and released when a transaction commits (like MDL, this approach is called the two-segment lock protocol). Row locks are also read-write locks and mutually exclusive between read-write locks. Row locking has small granularity and high concurrency, but it is slow to lock (MVCC) and easy to deadlock (two-segment lock).

It is important to note that a row lock locks the index, and if the operation is on the primary key index, then locks the corresponding entry on the primary key index. If you operate on a non-primary key index, then lock the non-primary key index and the corresponding entry on the primary key index. If no index is used, row locks become table locks.

Page locks

Page-level locks are between table and row locks in granularity and between them in performance. Deadlocks can occur as well as row locks.

Five lock modes

MetaData Lock

If a query is iterating through the data and another thread changes the table structure, the table structure of the query will change. Metadata locking is designed to prevent this from happening.

Metadata: Data that describes data. MetaData locks are used to ensure the consistency of Table MetaData before and after adding, deleting, modifying, and querying.

Metadata locks are also table-level locks that are automatically locked at the start of a transaction and unlocked at transaction commit. DQL and DML correspond to read locks, and DDL correspond to write locks.

Locking is done in DQL, DML, and DDL. Using start transaction disables automatic commit of DQL, DML, and DDL.

The same overwrite and block principle is followed in the table above.

Gap Lock

When the WHERE clause uses a range condition and requests a shared or exclusive lock, records that meet the range condition are locked, and gaps that meet the range but have no records are locked. Gap lock controls a section of left and right closing interval.

Gap locks only take effect at the repeatable read isolation level.

A gap lock is added to the index.

Gap locks can coexist, and a gap lock held by one transaction does not prevent another transaction from locking the same gap.

Other transactions are not allowed to insert records in the gap until the transaction that acquired the gap lock commits. Avoid magic reading.

Next-key Lock (also called post-code Lock)

Is a combination of a record lock and a gap lock that records the previous gap. Key lock controls a section of left open and right closed interval.

When a record lock is added to a non-unique index, the adjacent key lock of the record and the gap lock of the next interval are added simultaneously.

Auto-inc Lock

Auto-increment lock is a special table-level lock generated when inserting a table with auto-increment columns.

If a transaction is inserting a table that has self-incrementing columns, insertions by other transactions will block.

It is important to note that the auto-increment lock is released at the end of the statement execution, not after the transaction commits.

Insert Intent Lock

Insert intention lock is a special gap lock. When a record is inserted on an index range, an intent lock is inserted on that range. Other transactions that insert records on this interval block only if they meet a unique index and are in the same position.

Inserting the intent lock will also block the clearance lock operation.