What is the lock

A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads. In a database, in addition to the contention for traditional computing resources (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users. How to ensure the consistency and validity of concurrent data access is a problem that all databases must solve, and lock conflict is also an important factor that affects the performance of concurrent data access. From this perspective, locks are especially important and complex for databases. This chapter focuses on the features of MySQL lock mechanism, common lock problems, and some methods or suggestions to solve MySQL lock problems.

MySQL locking mechanism

Mysql uses many of these locking mechanisms, such as row locks, table locks, read locks, write locks, etc. These locks are collectively called Pessimistic locks.

Table-level locking is adopted by MyISAM and MEMORY storage engines.

Page-level locking is adopted by the BDB storage engine, but table-level locking is also supported.

InnoDB storage engine supports row-level locking and table-level locking.

  • Table lock: low overhead, fast lock; No deadlocks occur; Large locking granularity has the highest probability of locking and the lowest concurrency.
  • Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
  • Page lock: the overhead and lock time are between table lock and row lock. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

Only from the point of view of locking: table locking is more suitable for the query based, only a small amount of data update according to the index conditions of the application, such as Web applications; Row-level locking is more suitable for applications with a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

MyISAM table locks

MySQL Table level locks have two modes: Table Read Lock and Table Write Lock.

The read operation on the MyISAM table does not block other users’ read requests to the same table, but blocks the write requests to the same table. Write operations to the MyISAM table will block other users’ read and write operations to the same table.

When a thread acquires a write lock on a table, only the thread holding the lock can update the table. Read and write operations on other threads wait until the lock is released.

How to add table lock

MyISAM automatically locks all tables involved before executing a SELECT statement.

Before performing UPDATE operations (UPDATE, DELETE, INSERT, etc.), write locks are automatically assigned to the tables involved. This process does not require user intervention. Therefore, users generally do not need to use the LOCK TABLE command to explicitly LOCK the MyISAM TABLE.

The purpose of locking MyISAM table displays is to simulate transaction operations to some extent and to achieve consistent reading of multiple tables at one point in time. For example, there is an order table, Orders, which records the total amount of each order, and an order detail table, order_detail, which records the subtotal amount of each product of each order. Suppose we need to check whether the total amount of the two tables is consistent. You may need to execute the following two SQL statements:

Select sum(total) from orders;
Select sum(subtotal) from order_detail;Copy the code

At this point, if you do not lock both tables first, you may get wrong results because the ORDER_detail table may have changed during the execution of the first statement. Therefore, the correct approach should be:

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;Copy the code

In this example, the “local” option is added to LOCK TABLES to allow concurrent insertions of MyISAM TABLES to the end of the table as long as the conditions for concurrent insertions are met. SQL > alter table table_name LOCK (); SQL > alter table LOCK (); That is, after executing LOCK TABLES, only explicitly locked TABLES can be accessed, not unlocked TABLES. Also, if a read lock is added, only queries can be performed, not updates. In fact, this is almost the case in the case of automatic locking, MyISAM always acquires all the locks required by the SQL statement at once. This is why no Deadlock Free occurs in MyISAM tables.

MyISAM lock scheduling

As mentioned earlier, the MyISAM storage engine’s read and write locks are mutually exclusive, and the read and write operations are serial.

A process requests a read lock on a MyISAM table while another process requests a write lock on the same table. The answer is that the writer gets the lock first.

In addition, even if the read request reaches the lock wait queue first, the write lock will be inserted before the read lock request! This is because MySQL considers write requests generally more important than read requests. This is why MyISAM tables are not ideal for applications with a lot of update and query operations. Because a large number of update operations can make it difficult for query operations to acquire read locks, they can block forever. This can sometimes go horribly wrong! Fortunately, we can adjust MyISAM’s scheduling behavior with some Settings.

  • By specifying the startup parameter low-priority-updates, the MyISAM engine gives priority to read requests by default.
  • By executing commandsSET LOW_PRIORITY_UPDATES=1To lower the priority of update requests made by the connection.
  • Lower the priority of INSERT, UPDATE, and DELETE statements by specifying the LOW_PRIORITY attribute.

Although the above three methods are either update-first or query-first, they can still be used to solve the serious problem of read lock waiting in applications where the query is relatively important (such as user login system). In addition, MySQL also provides a compromise method to adjust read/write conflicts, that is, to set the system parameter max_write_lock_count to an appropriate value. When a read lock reaches this value, MySQL temporarily lowers the priority of the write request, giving the read process a certain chance to obtain the lock.

The problems and solutions associated with write-first scheduling have been discussed above. One more point: some queries that take a long time to run can starve the writing process!

Therefore, the application should try to avoid the occurrence of a long running query operation, do not always want to use a SELECT statement to solve the problem, because this seemingly clever SQL statement, is often more complex, the execution time is long, in the case of possible through the use of intermediate tables and other measures to do certain “decomposition” of SQL statements, Each step of the query can be completed in a short time, thus reducing lock conflicts. If complex queries are unavoidable, try to schedule them during database downtime, such as some scheduled statistics that can be scheduled at night.

InnoDB lock

There are two major differences between InnoDB and MyISAM: One is TRANSACTION support; The second is the use of row-level locking.

What is a transaction

A database transaction is a sequence of database operations that access and potentially operate on various data items, all of which either succeed or fail, and are an indivisible unit of work.

Generally speaking, it refers to a database logical processing unit composed of a group of SQL statements. In this group of SQL operations, either all the execution succeeds or all the execution fails.

A simple and classic example is transfer. To transfer money in transaction A, money must be deducted from the outgoing account and money must be added to the transferred account. Both operations must be successfully executed at the same time to ensure data consistency.

Characteristics of transactions

The four major features of transactions in Mysql include Atomicity, consistency, Isalotion, Durable (ACID for short).

  • Atomicity:Atomicity refers to the atomic operation of the transaction, the modification of the data is either all successful or all failed, to achieve the atomicity of the transaction, is based on the logRedo/Undomechanism
  • Consistency: Indicates that the state before and after a transaction is consistent. It can be understood as data consistency. Isolation focuses on the isolation of transactions from each other and is closely related to the isolation level set for transactions.
  • Isolation: Generally speaking, the operations of one transaction are invisible to other transactions, that is, transactions are generally independent. However, this depends on the isolation level of the database, which is not visible except for read uncommitted isolation levels.
  • Persistence: After a transaction is committed, the state of the transaction will be persisted to the database, that is, the transaction commit, new data, updates will be persisted to the library.

The Undo/Redo log

  • Undo logs record the value of a data before modification and can be used to rollback when a transaction fails.
  • Redo logs record the modified value of a block of data. They can be used to restore data that was successfully updated by transactions that were not written to the data file.

Undo Log is to achieve atomicity of transactions. In MySQL database InnoDB storage engine, Undo Log is also used to achieve multi-version concurrency control (referred to as MVCC).

Using the Undo/Redo command, you can Undo and restore operations on the interface.

If at some point the database crashes, transaction A and transaction B are executing before the crash, transaction A has committed, but transaction B has not committed. When the database is restarted for crash-recovery, committed transaction changes are written to the data file through the Redo log, and uncommitted transactions are rolled back through the Undo log.

Transaction isolation level

  • READ UNCOMMITTED: When a transaction is not committed, its changes can be seen by other transactions. Read uncommitted is unlocked, so there is no isolation for it, so it has the best performance.
  • READ COMMITTED: After a transaction commits, the changes it makes can only be seen by other transactions.
  • REPEATABLE READ: The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, at the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
  • SERIALIZABLE: For the same row, a write lock is added to “Write” and a read lock is added to “read”. When a read-write lock conflict occurs, the later transaction must wait for the previous transaction to complete before execution can continue.

What issues are addressed by the isolation level

Dirty read: If a transaction reads data that has been modified by another uncommitted transaction.



Non-repeatable read: If a transaction can only read the data modified by another committed transaction, and the transaction can query the latest value every time other transactions modify and commit the data.



Phantom read: If a transaction first queries records based on some criteria and then another transaction inserts records that meet those criteria, the original transaction can read the records inserted by the other transaction when it queries again based on the criteria.



Isolation levels address the above issues separately:

Implementation of row lock

InnoDB implements two types of row locking.

  • Shared lock (S) : also called read lock. An exclusive lock that allows one transaction to read a row, preventing other transactions from acquiring the same data set. If transaction T holds S lock on data object A, then transaction T can read A but cannot modify A. Other transactions can only hold S lock on A but cannot hold X lock on A until T releases S lock on A. This guarantees that other transactions can read A, but cannot make any changes to A until T releases the S lock on A.
  • Exclusive lock (X) : also known as write lock. Allows transaction update data to acquire exclusive locks, preventing other transactions from acquiring the same set of shared read locks and exclusive write locks. If transaction T locks data object A with X, transaction T can either read A or modify A, and no other transaction can lock A again until T releases the lock on A.

References:

MySQL > alter database lock

www.cnblogs.com/leedaily/p/…

, a MySQL row lock, the dead lock and the deadlock detection: blog.csdn.net/xcy11930686…