In-depth understanding of MySQL various locks

In the database, in addition to the contention of traditional computer resources (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users. If the consistency and validity of concurrent data access are to be guaranteed, it is a problem that all databases must solve. Lock conflict is also an important factor affecting the concurrent access performance of the database. From this perspective, locks are particularly important and more complex for databases.

Table locks:
	During the operation, the entire table row lock is locked:
	When operating, the current operation line will be locked.

Read lock (shared lock):
	For the same data, multiple read operations can be performed simultaneously without affecting each other's write locks (exclusive locks):
	Until the operation is completed, it will block other write locks and read locks

Before executing a query statement (select), MyISAM will automatically lock all tables involved. Before executing an update operation (update, delete, insert, etc.), it will automatically add a write lock to the tables involved. This process does not require user intervention. Therefore, users generally do not need to directly use the LOCK TABLE command to explicitly lock the MyISAM table.

Add read lock:
	lock table table_name read; 
	---Unlock tables;
Add write lock:
	lock table table_name write;
	Add a write lock. The current session can read and write, and other sessions will be blocked (waiting). Read locks restrict writing, and write locks restrict reading and writing.

1. Read operations on MyISAM tables will not block other users' read requests for the same table, but will block write requests for the same table;
2. Write operations on MyISAM will block other users' read and write operations on table 1;
3. MyISAM lock scheduling is write-first.
	It is not suitable as the main table. After writing the lock, a large number of updates by other threads will make it difficult for the query to obtain the lock, which may cause permanent blocking.

Check the usage of locks show open tables;
Check the lock status of the table show status like 'table_locks%';
		Table_locks_immediate: The number of times a table-level lock can be acquired. The value increases by 1 for each immediate lock acquisition.
		Table_locks_waited: The number of times you need to wait if you cannot obtain the lock immediately. The value increases by 1 each time you wait. This can be used to determine the severity of table-level lock contention.	

Shared locks:
	Also known as a read lock, multiple transactions can share a lock, but can only read, not write exclusive locks:
	Also known as write lock, locks are not shared. Transactions that cannot obtain locks cannot perform read and write operations. If update, delete, and insert statements are performed, innoDB will automatically add exclusive locks to the data sets involved. No locks will be added to different select statements. Locks are added to queries explicitly. Add shared locks:
		select * from table_name where ... Lock IN SHARE MODE
	Add an exclusive lock:
		select * from table_name where ... FOR UPDATE

Index failure, row lock upgrade to table lock where there is no index behind it is also upgraded to table lock

show status like 'innodb_row_lock%';
	Innodb_row_lock_current_waits The number of locks currently waiting Innodb_row_lock_time The total duration of the lock Innodb_row_lock_time_avg The average duration of the lock Innodb_row_lock_time_max The maximum duration of the lock Innodb_row_lock_waits The total number of waits since the system was started

Try to make all data retrievals be completed through indexes to avoid upgrading non-index row locks to table locks. Design indexes reasonably and minimize the scope of locks. Minimize index conditions and index scopes to avoid gap locks. Try to control transaction size and reduce the amount of locked resources and the length of time. Use low-level transaction isolation as much as possible (business needs to meet the needs).