MySQL Learning notes-Lock related Topics

In the transaction-related topic, transaction isolation has been mentioned to be implemented by lock mechanism. In this paper, we focus on the different innodb and myisam locking mechanisms, and then describe the way the lock is implemented, the concept of multiple locks, and the cause of the deadlock.

the lock mechanism of MySQL common storage engine

MyISAM and memory using table-level lock (table-level locking);

BDB using page lock (page-leve locking) or table-level lock, the default is page lock;

InnoDB supports row-level locks (row-level locking) and table-level locks, which by default are row-level locks;

Various lock features

Table level Lock (Table-level locking): Low overhead, lock fast, no deadlock, lock granularity is high, the probability of conflict is highest, the concurrency is lowest

Row-level locks (row-level locking): High overhead, locking slow, deadlock, minimum lock granularity, lowest probability of lock collisions, and highest concurrency

Page lock (page-level locking): Overhead and lock time between table and row locks, deadlock occurs, lock granularity between table and row locks, concurrency is common

Algorithm of the Lock

The InnoDB storage engine has 3 lines of algorithmic design, namely:

• Record Lock: A lock on a single row record
• Gap Lock: A lock that locks a range but does not contain the record itself
• Next-key lock:gap Lock+record Lock, locks a range, and locks the record itself

Record lock always locks the index record, and if no index is set when the InnoDB Storage engine table is established, the InnoDB storage engine locks with an implicit primary key, Next-key lock at REPEATABLE read isolation level. The algorithm is the default row record locking algorithm.

Blocking, system mutex (mutex), and Semaphore (event)

Because of the compatibility relationship between different locks, a lock in one transaction needs to wait for a lock in another transaction to release the resources it occupies. In the source code of the InnoDB storage engine, a mutex data structure is used to implement the lock. You need to apply the Mutex_enter function before accessing the resource, and execute the Mutex_exit function immediately after the resource access or modification is complete. When a resource is already occupied by one transaction, another transaction executes the Mutex_enter function and waits, which is blocking. Blocking is not a bad thing, blocking is to ensure that transactions can be concurrent and run properly.

In the InnoDB engine, the basic mutex (mutex) and event (semaphore) provided by the operating system are encapsulated, and the implementation under Windows is not recorded for the time being, mainly to support POSIX systems. The implementation of the POSIX system is os_fast_mutex_t and os_event_t. Os_fast_mutex_t is relatively simple, in fact, is Pthread_mutex. Defined as follows:

1. typedef pthread_mutex os_fast_mutex_t;

While os_event_t is relatively complex, it is implemented through os_fast_mutex_t and a pthread_cond_t, defined as follows:

1. typedef struct os_event_struct
2. {
3. os_fast_mutex_t os_mutex;
4. ibool is_set;
5. pthread_cond_t cond_var;
6. }os_event_t;

The following is an example flow of two-thread signal control for os_event_t:
For the system encapsulation, the most important is the os_event_t interface package, and in the os_event_t package, Os_event_set, Os_event_reset, os_event_wait These three methods are the most critical.
CAs Atomic Operations In the implementation of InnoDB mutexes (mutexes), atomic operations are used to encapsulate an efficient mutex implementation in addition to the os_mutex_t of the referencing system. In the case that the system supports atomic operations, it uses its own encapsulated mutex to do the mutex, and if not, use os_mutex_t. Because I am not familiar with the operating system programming, in this Java Util.concurrent.atomic package example, which encapsulates a number of atomic operations of the class, a large number of similar to the mutex (mutex) operation. CAS atomic Operations--compare & Set, spin locks are implemented in the loop body by the current thread, and can enter the critical section when the condition of the loop is changed by another thread.

2. /**
4. * Atomically updates the current value with the results of
6. * applying the given function, returning the previous value. The
8. * function should be side-effect-free, since it may be re-applied
10. * when attempted updates fail due to contention among threads.
12. *
14. * @param updateFunction a side-effect-free function
16. * @return the previous value
18. * @since 1.8
20. */
22. public final int getAndUpdate(IntUnaryOperator updateFunction) {
24. int prev, next;
26. do {
28. prev = get();
30. next = updateFunction.applyAsInt(prev);
32. } while (!compareAndSet(prev, next));
34. return prev;
36. }
40. /**
42. * Atomically sets the value to the given updated value
44. * if the current value {@code ==} the expected value.
46. *
48. * @param expect the expected value
50. * @param update the new value
52. * @return {@code true} if successful. False return indicates that
54. * the actual value was not equal to the expected value.
56. */
58. public final boolean compareAndSet(int expect, int update) {
60. return unsafe.compareAndSwapInt(this, valueOffset, expect, update);
62. }

InnoDB locks in the storage engine

InnoDB is a multi-threaded concurrent engine, the internal read and write are implemented with multithreading, so innodb internal implementation of a more advanced concurrency synchronization mechanism. InnoDB does not directly use the system-provided lock (latch) synchronization structure, but rather its own encapsulation and implementation optimization, but also compatible with the system's locks. Let's take a look at a InnoDB internal note (MySQL-3.23):

Semaphore operations in operating systems is Slow:solaris on a 1993 SPARC takes 3 microseconds (US) for a Lock-unlock PA IR and Windows NT on a 1995 Pentium takes microseconds for a lock-unlock pair. Therefore, we have toimplement our own efficient spin lock mutex. The future operating systems mayprovide efficient spin locks and we cannot count on that.

Probably mean that 1995 years, a Windows NT Lock-unlock need to consume 20us, even under the Solaris also need 3us, this is why he wants to implement the purpose of custom latch, In InnoDB, the author implements the encapsulation of the system latch, the custom mutex, and the custom Rw_lock.
The InnoDB storage engine implements the following two standard line-of-machine locks:

Shared Lock (S Lock): Allows a transaction to read a row of data. Exclusive lock (X Lock): Allows a transaction to delete or update a row of data. Not to be continued ...

MySQL Series blog:

MySQL Learning note-outline MySQL learning note-mysql Architecture Overview MySQL Learning note-database background thread MySQL learning note-database memory MySQL learning note-cache with Buffermysql Learning Notes-database files MySQL Learning notes-transaction related Topics MySQL learning Notes-mysql database optimization practice [go] references: MySQL series: InnoDB engine analysis thread concurrency synchronization mechanism Oracle Mutex implementation mechanism "MySQL Technology insider InnoDB storage Engine"

MySQL Learning notes-Lock related topics