MySql Performance Tuning (5) demonstration of using a proper lock mechanism for table locks, mysql Tuning

MySql Performance Tuning (5) demonstration of using a proper lock mechanism for table locks, mysql Tuning

MySql locks take the following forms:

1. Table-level locks; low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict, the lowest concurrency. The MyISAM engine belongs to this type.

2. Row-level locks: high overhead and slow locking; deadlocks may occur; minimum lock granularity, minimum lock conflict probability, and highest concurrency. The InnoDB engine belongs to this type.

3. Page lock: the overhead and lock time are between the table lock and the row lock. There will be deadlocks. The lock granularity is also between the two, and the concurrency is normal. NDB belongs to this type.

1. Table lock demonstration

The MyISAM storage engine only supports table locks. Therefore, you can perform the following operations:

1. Read operations on the MyISAM table will not block read requests from other processes to the same table, but will block write requests to the same table. Write operations of other processes are performed only after the read lock is released.

2. Write operations on the MyISAM table will block the read or write operations performed by other processes on the same table. Only when the write is released will the read and write operations of other processes be performed.

[Example ]:

Open another session:

Session 2 waits until the lock is released.

Execute Session 2 at the same time:

Ii. Demonstration of row locks

The InnoDB Storage engine is implemented by locking index items. This means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB uses table locks.

1. Row lock

In the myiSAM engine, two sessions will respond when updating the same record, because myiSAM is a table lock.

But in InnoDB:

In Session 2,

Lock wait. Because the same record is updated.

2. search data without an index

The reason is that InnoDB uses row-level locks to retrieve data through index conditions. Otherwise, InnoDB uses table locks.

3. deadlock

In this case, Session 1 will always wait...

Session 2 changes will time out...

View session 1 again

After a deadlock occurs, InnoDB will automatically detect that it will release the lock for one transaction and roll back the transaction. The other transaction will get the lock and continue to complete the transaction. Deadlocks cannot be avoided. We can adjust the business logic to minimize the probability of deadlocks.