In terms of lock granularity, MySQL contains three main types (levels) of locking mechanisms:
- Global locking locks the entire database. Implemented by MySQL SQL Layer
- A table-level lock locks a table. Implemented by MySQL SQL Layer
- Row-level locking locks rows and may lock gaps between rows. Implemented by some storage engine, such as InnoDB
Table – level lock and row – level lock differences: table – level lock: low overhead, fast lock; No deadlocks occur; Large lock granularity has the highest probability of lock conflict and the lowest concurrency. Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
According to the function of the lock, it can be divided into:Shared read locksandExclusive write locksAccording to the implementation mode of lock, it can be divided into:Pessimistic lockingandOptimistic locking(Logical control using a version column or a unique column)
