Lock is a mechanism for computers to coordinate multiple processes or threads to concurrently access a certain resource. It is a key feature that distinguishes database systems from file systems. It can ensure that users can read and modify data in a consistent manner. In databases, in addition to traditional contention for computing resources (CPU, RAM, and I/O), data is also a resource shared by many users.
How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in the existing database. Lock conflict is also an important factor that affects the performance of database concurrent access. From this perspective, locks are particularly important and more complex for databases. Preventing update loss cannot be solved solely by the database transaction controller, but requires the application to apply necessary locks to the data to be updated.
MySQL uses many such locking mechanisms, such as row locks, table locks, read locks, and write locks, all of which are applied before operations are performed. According to the types of database operations, there are read locks and write locks:
Read lock (shared lock): For the same piece of data, multiple read operations can be performed simultaneously without affecting each other;
Write lock (exclusive lock): Before the current write operation is completed, it will block other write and read locks;
Different storage engines support different locking mechanisms. For example, MyISAM and MEMORY storage engines use table level locking; The BDB storage engine uses page level locking, but also supports table level locking; The InnoDB storage engine supports both row level locking and table level locking, but row level locking is used by default.
Table level locking: Low overhead, fast locking; No deadlock occurs; The locking granularity is large, with the highest probability of lock conflicts and the lowest concurrency.
Row level locking: High overhead and slow locking; Deadlocks may occur; The lock granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is also the highest.
Page locking: The overhead and lock time are bounded between table and row locks; Deadlocks may occur; Locking granularity is bounded between table locks and row locks, with a moderate concurrency
In summary, it is difficult to simply say which lock is better, but only in terms of the characteristics of the specific application, which lock is more suitable. From a lock perspective alone, table level locks are more suitable for applications that focus on queries and only update data based on index criteria, such as Web applications; Row level locking is more suitable for applications where there are a large number of concurrent updates to a small amount of different data based on index conditions, as well as concurrent queries, such as some online transaction processing (OLTP) systems.
