31st Day MySQL concurrency control, storage engine Introduction, user Rights Management, cache management, and data type selection

I. Concurrency control for MySQL

1, MySQL concurrency control at the server layer and the storage engine layer completed

Locks: Lock

Read lock: Shared lock, can be read by several user colleagues, but cannot write

Write Lock: Exclusive lock,

Lock granularity: Lock table when lock table range (such as a user read the first three rows, one user writes the last line)

Table-level Lock: InnoDB table-level lock

Row-level Lock: Myisa row-level lock (lock control Complex)

Deadlock: A request B,b request a (oneself can negotiate unlock)

Lock classification:

Implicit lock: Auto-complete by the storage engine

Explicit Lock: User can manually apply a lock, table-level lock

Manual locking: Server-level implementation

LOCK TABLES tb1 {read| WRITE},......

UNLOCK TABLES; release lock

Example: >use hellodb;

>lock TABLES students READ;

FLUSH TABLES with READ LOCK; Close the table and perform a read lock globally

UNLOCK TABLES;

The InnoDB storage engine also supports another explicit lock (only selected rows are locked)

SELECT ... LOCK in SHARE MODE;

SELECT ... For UPDATE;

2. Business: ACID

A transaction is an execution unit: Multiple SQL statements are either executed or not executed.

31st Day MySQL concurrency control, storage engine Introduction, user Rights Management, cache management, and data type selection