MySQL lock related issues learning

• According to the locking granularity of data operations :(locking granularity increases in order)

  • 1. Row-level locks
  • 2. Lock clearance
  • 3. Page-level locks
  • 4. The table level lock

• According to the lock sharing strategy:

  • 1. A Shared lock
  • 2. The exclusive lock
  • 3. Intent to share lock
  • 4. Intention exclusive lock

• From the locking strategy:

  • Optimistic locking
  • Pessimistic locking

• Other:

  • On the lock

• Table-level locking is used for MYISAM and MEMORY.
• BDB: Page -level locking or table -level locking. Page locking is adopted by default
• InnoDB supports row-level locking and table-level locking. The default row-level locking is performed

Clearance Lock

A gap lock locks a range, but does not include the record itself (its lock granularity is larger than the record lock locks the entire row, it is locked in a range of multiple rows, including non-existent data), a gap lock must be open interval, such as (3,5).

The purpose of the GAP lock is to prevent the illusion of two current reads of the same transaction. The lock will only exist at isolation levels RR(repeatable read) or above. The purpose of gap locking is to prevent other transactions from adding data to the gap.

This section describes table locking

Table level lock is the lock with the largest granularity in mysql. It locks the entire table in the current operation. It is simple to implement, consumes less resources, and is supported by most mysql engines. The most commonly used MYISAM and InnoDB both support table-level locking.

Table locks can be divided into:

• Table shared read lock (shared lock)
• Table exclusive write lock (exclusive lock)

Table locking features

Low overhead, fast lock, no deadlocks. The probability of lock conflict is the highest and the concurrency is the lowest.

• LOCK TABLE my_table_name READ;Locking tables with read locks blocks other transactions from modifying table data.
• LOCK TABLE my_table_name WRITE;Locking the table with a write lock blocks other transaction reads and writes.

Table level locking in different storage engines

• InnoDB storage engine does not add table level S or X locks to a table when executing SELECT, INSERT, DELETE or UPDATE statements. Table level locks need to be explicitly declared manually.
• MyISAMWhen executing a query (SELECT) will be automatically given to all tables involvedAdd read lock, while performing the update operation (UPDATE, DELETE, INSERTAnd so on) will automatically give the table involvedAdd write lock, this process does not require user intervention, therefore, users generally do not need to directly useLOCK TABLECommand to explicitly lock the MYISAM table.

SQL Basic Operations

• Build table SQL:

CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
Copy the code

• Insert data:

INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1'.'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2'.'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3'.'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4'.'d');
Copy the code

• Manually add table locks (read locks/write locks)

lock tableTable name read/Write, table name2 read/write;
Copy the code

• View the locks added to the table

show open tables;
Copy the code

• Delete table locks

unlock tables;
Copy the code

• LOCK TABLES t1 READ: lock S for table T1.
• LOCK TABLES t1 WRITEAdd table level X lock to table T1.

Try not to use these two ways to lock, because InnoDB’s advantage is row lock, so try to use row lock, performance is higher.

Shared lock and exclusive lock in MySQL specific implementation is read lock and write lock:

• Read Locks: Shared Locks (S Locks) allow multiple read operations to be performed simultaneously for the same data without affecting each other
• Write Locks: Exclusive Locks that block other write and read Locks until the current write operation is complete
• IS Lock: Intention Shared Lock When a transaction IS about to place an S lock on a record, an IS lock needs to be placed at the table level.
• IX Lock: Intention Exclusive Lock. When a transaction is about to place an X lock on a record, an IX lock is required at the table level.

IS and IX locks are table-level locks. They are proposed only to quickly determine whether the records in the table are locked when the S-level locks and X-level locks are added later, so as to avoid traversing to check whether there are locked records in the table. When a row is locked, if you want to add a table lock to the table where the row resides, you must first check whether the row is locked, otherwise there will be conflicts. IS and IX locks eliminate the need to traverse each row in a table to determine whether the row IS locked or not. You can determine whether a table has a row lock by checking whether the table has an intent lock.

Note: If there are more than one row lock in a table, they all add an intent lock to the table. There is no conflict between the intent lock and the intent lock.

Intentional shared lock/intentional exclusive lock belongs to table lock, and obtaining intentional shared lock/intentional exclusive lock is the prerequisite of obtaining shared lock/exclusive lock.

• (IS) Intention Shared Lock: When a transaction intends to place an S Lock on a record, it needs to place an IS Lock on the table level.
• When a transaction intends to place an X Lock on a record, an IX Lock needs to be placed at the table level.

IS and IX locks are table-level locks. They are proposed only to quickly determine whether the records in the table are locked when the S-level locks and X-level locks are added later, so as to avoid traversing to check whether there are locked records in the table. When a row is locked, if you want to add a table lock to the table where the row resides, you must first check whether the row is locked, otherwise there will be conflicts. IS and IX locks eliminate the need to traverse each row in a table to determine whether the row IS locked or not. You can determine whether a table has a row lock by checking whether the table has an intent lock.

Shared/exclusive locks are compatible with intentional shared/intentional exclusive locks:

These four types of locks are pessimistic locks. If there are multiple rows in a table, they will add intent locks to the table. There will be no conflict between intent locks, and exclusive locks will conflict with everyone.

Optimistic locks assume that concurrent operations on the same data will not change (add, delete, change less, search more). When data is updated, it is modified by constantly trying to update the data. If there is no conflict, the operation succeeds. If there is a conflict, another thread is already using the resource, so the polling is continued. Optimistically, concurrency without locking is fine. Optimistic locking is the practice of recording multiple versions of a data history, and then returning to the unmodified version if any conflicts are found after modification. The upside is reduced context switching, the downside is wasted CPU time.

Optimistic locking, compared with pessimistic locking, considers that data will not cause conflicts in general. Therefore, when data is submitted for update, it will formally check whether data conflicts. If conflicts are found, an error message will be returned to let the user decide what to do.

Optimistic locking is the idea that data will be updated if it is not locked, and not updated if something is wrong (rollback). This is often done by adding a Version field (version number) to the database. Optimistic locks can be used to avoid lost updates. Let’s look at the implementation of optimistic locking in tables and caches.

Optimistic locks are widely used for state synchronization. We often encounter a scenario where the state of a logistics order is changed simultaneously, so optimistic locks play a huge role in this situation. However, the selection of optimistic lock field also needs to be very careful, a good optimistic lock field can reduce the lock granularity.

In the case of commodity inventory deduction, especially in high concurrency scenarios such as seckilling and juhuaxan, if version number is used as optimistic lock, only one transaction can be updated successfully every time, which means a large number of operations fail in business perception. Because the granularity of version is too large, the probability of update failure increases.

But if we pick the inventory field as an optimistic lock (comparing the inventory number to determine the version of the data), then our lock granularity will be reduced and the probability of update failure will be greatly reduced.

-- Use inventory as optimistic lock
-- Step1: Query commodity information
select (inventory) from items where id=100;

-- Step2: Generate orders according to commodity information
insert into orders(id,item_id) values(null.100);

Step3: Modify the inventory of goods
update items set inventory=inventory- 1 where id=100 and inventory- 1>0;
Copy the code

Taobao seconds kill, ju cost-effective, run is this SQL, through the selection of optimistic lock, can reduce the lock strength, so as to improve throughput.