Mysql read-write lock and concurrency control
1. Concurrency control problems occur whenever more than one query modifies data at the same time
2. How to control read and write concurrently in mysql server layer and storage engine layer
3. Here is an example of an mbox mailbox file:
If more than one process writes to an Mbox file at the same time, then at the end of the file, there will be a jumble of overlapping additions, such that process 1 writes several lines, and process 2 writes several lines, crossing each other, and the data will be wrong. A well-designed Mbox requires a lock. For example, if process 1 locks a file, process 2 must wait for process 1 to finish before the lock is released. Concurrency is not supported and only one process can write data at a time
4. There may be problems when reading data. For example, when one process is reading data, another process wants to delete data at the same time. Shared locks are called read locks, and exclusive locks are called write locks
5. The read lock is shared and does not block other read locks. Write locks are exclusive and block other read and write locks; Reading is not mutually exclusive, reading is mutually exclusive, writing is mutually exclusive
6. The mysql database is locked all the time. When a user modifies data, other users will be blocked from accessing the data
Mysql has two types of lock granularity: lock whole table and lock row
Table lock: When a user modifies data, the write lock will be obtained, and the entire table will be locked, and other users cannot read and write,myisam
Row lock: When a user modifies a few rows of data, a write lock will be obtained. At this time, only those rows are locked, and other users cannot read and write those rows. Other rows are not affected, but admin locks consume resources, innoDB
8. Run the unlock tables command to unlock all rows lock tables table name read or write
Test read/write read exclusivity
1. Add read locks
2021 Jinsanyin four big factory interview real questions collection, must see!
Four years of PHP technical articles collation collection – PHP framework
A collection of four years’ worth of PHP technical articles – Microservices Architecture
Distributed Architecture is a four-year collection of PHP technical articles
Four years of PHP technical essays – High Concurrency scenarios
Four years of elite PHP technical article collation collection – database