What is a latch? The difference between latch and lock

Latch

What is latch:

Locks are a key feature that distinguishes database systems from file systems. Locking mechanisms are used to manage concurrent access to shared resources. InnodbThe storage engine locks table data at the row level, which is good. But Innodb also uses locks in multiple places, allowing multiple different resources to provide concurrent access. For example, operate the LRU list summarized by the buffer pool, delete, add, and move elements in the LRU list. In order to ensure consistency, There must be a lock involved, which is the latch lock. The difference between

latch and lock

latch is generally called It is a latch (a lightweight lock) because the locking time required must be very short. If it lasts for a long time, the performance of the application will be very poor. In the Innodb storage engine, latch can be divided into mutex (mutual Exclusion) and RW-Lock (read-write lock).

The lock object is a transaction, which is used to lock objects in the database, such as tables, rows, and pages. And generally lock objects are only released after transaction commit or rollback (The release time for different transaction isolation levels may be different). Additionally, lock, as in most databases, is a deadlock mechanism.

##Same as show engine innodb mutex Checklatch

The name

column displays the information of latch and the location (number of lines) of the source code. The classification of

latch

is divided into: mutex: mutex; sometimes Some resources require sharing and concurrency, but they are not frequent, so apply for a mutex from the operating system. mutex are all exclusive. .

RW-LATCH:

Read-write lock

latch understanding:

An example:

When we execute select , the data is cached in buffer pool Yes, multiple threads concurrently accessing or modifying this data must require a concurrency control mechanism. This is latch

The data to be accessed by the database must first be stored in the cache, and the cache is generally smaller than the disk space. The data buffer uses the hash table to record whether the data page is in memory. The corresponding RW-Latch in MySQL is very good in errlog Clearly, the RW-Latch is in line 658 of buf0sea.cc RW-Latch created.

Look at the stress test example of errorlog :

According to the log, we can analyze the thread 140140355766016 We need to add a x lock to the record, but the waiting thread 0x4c407b8 Release of RW-Latch of thread.

latchContent process

1）a Access the linked list with x

2)b Waiting in line for x to unlock occupied cpu, but cpu found you waiting, So the time for cpu to kick b out of the

3) chain is to find data time.

4）bI know very quicklyaSo,bDon’t queue, this is to go to spin That is, idle cpu, and then look at the memory data Structure, ahas been unlocked

5)bAfter turning around, During the time period of bspin, c came in, and spin# happened many times in a row. ## After generating os waits

6

) the operating system will b from Kicked out in cpu

latch Lock features:

• 1.Don’t queue

• 2.spin

• 3 .os waits

• ##4.cpu

  busy

##mutex ：

Memory structure Very small The database is applied for from the operating system and does not occupy the buffer pool, completely exclusive

Mutex lock holding process:

A thread holds a 1# that it wants to write in the mutex memory data structure

## 　 b

The thread sees that the memory data structure has numbers, then go to spin

confirmlatch Content type:

(This is statement 173 in the source code) obtained from errorlog

latchThe contention process

There is a chain protection mechanism on the linked listlatch, a small memory structure, At this time, a reading thread a comes up to read the chain. At this time, the management becomes r, read lock, when When data is found on the chain, (read), release the read lock as soon as it is found, b also needs to be read when it comes up. At this time, it looks like r. The read lock can be shared, and she also accesses and reads the chain, c came up to modify the contents of the two blocks in the chain. I saw that they were r, r and w are mutually exclusive and cannot be performed at the same time, or

1

, actively request to exitcpu

2

, occupying cpu resources (execute an empty code, loop, separated Take a while to see if a and b have been used up (spin), but During this process, because c is not queued to wait, other threads may come up to occupy the chain while waiting. If it is still like this after executing multiple times, it may be sleep, exitcpu) Why is it idle (afraid that the operating system will see her idleness and force him to be dragged away), etc. (because he knowsa and b occupy resources for a relatively short time, that is, the time to traverse a chain is very short).

latch

Contention phenomenon:

1

, latch Contention will appear as cpuBusy

2

、latchContent is not queued, wait for a random period Come back and take a look at the time

Monitoring indicators

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 2

OS WAIT ARRAY INFO: signal count 2

RW-shared spins 0, rounds 4, OS waits 2

RW-excl spins 0, rounds 0, OS waits 0

RW-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait: 4.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx

rounds

means asking for the parameters of each rotation

os waits: means sleep. When the sudden growth is relatively fast, it means latch that the contention is serious.

rw-shared spin number of times

rw-excl spinnumber of times

latchCauses of contention

1, memory access is too frequent (keep looking)

2,listThe chain is too long (10000 is hung on the chain, the chance of being held is too high)

So sometimes we increase the number of instance and cut the big pool into smaller onespool, make the list chain shorter

How to reduce latch contention:

If latch contention is serious

1.Optimizationsql, reduce The number of memory reads——The effect is more obvious

2.Increaseinstances The number

How to accurately find the solutionlatch

1.show engine Innodb mutex

2 .Check what type of latch

3.Locationsql

The above is the detailed content of What is a latch? The difference between latch and lock. For more information, please follow other related articles on the PHP Chinese website!