MySQL Performance tuning (v) demonstration of a table lock with the appropriate locking mechanism

The MySQL lock is available in the following ways:

1. Table-level lock, low overhead, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, and the concurrency is the lowest. The MyISAM engine belongs to this type.

2. Row-level Lock: High overhead, locking slow, deadlock, locking granularity is minimal, the probability of lock collision is the lowest, and the concurrency is the highest. The InnoDB engine belongs to this type.

3. Page Lock: The cost and lock time between the table lock and the row lock, there will be deadlock, locking granularity is also in between the two, the concurrency is general. NDB belongs to this type.

A Presentation of table Locks

The MyISAM storage engine supports only table locks, so there are several things you can do about it:

1. Read operations on the MyISAM table do not block other processes from reading requests to the same table, but block write requests to the same table. Write operations for other processes are performed only when the read lock is released.

2. Writes to the MyISAM table block other processes from reading or writing to the same table, and the read and write operations of other processes are performed only after the write is released.

"Example":

Open another session:

Session 2 will wait until the session is released with a lock.

Simultaneous session 2 execution:

Two Demo of row Locks

The InnoDB storage engine is implemented by locking the index entries, which means that the InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock.

1. Row lock

MyISAM engine two sessions update the same record will respond because MyISAM is a table lock.

But in the InnoDB:

In Session 2,

This will lock the wait. Because the same record is updated.

2. Retrieving data without index

The reason is that InnoDB uses row-level locks to retrieve data through index criteria, otherwise innodb will use table locks.

3. Deadlock

At this point session 1 will always wait ...

Session 2 will time out when making changes ...

See Session 1 again

After a deadlock occurs, InnoDB automatically detects that it frees a transaction to release the lock and rolls it back, and the other transaction obtains the lock and continues to complete the transaction. Deadlocks are unavoidable, and we minimize the probability of deadlocks by adjusting the logic of the business.

MySQL Performance tuning (v) demonstration of a table lock with the appropriate locking mechanism