In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Source code of this article: GitHub click here | | GitEE click here |
Introduction to the concept of lock 1. Basic description
The core function of the locking mechanism is to coordinate the occupation of resources when multiple threads concurrently access the same resource in multiple sessions. Lock mechanism is a very large module, which implements several core and difficult modules of MySQL: index, lock mechanism, transaction. Here are several typical scenarios based on the demonstration of MySQL5.6, and it is critical to have analysis processes and ideas when facing these questions of MySQL. These lock concepts are common in MySQL: shared read locks, exclusive write locks, table locks, row locks, and gap locks.
2. Storage engine and lock MyISAM engine: based on read-write mode, table-level locking is supported; InnoDB engine: supports row-level read-write lock, cross-row gap lock, InnoDB also supports table lock; 3, lock operation APILOCK TABLE name [READ,WRITE]; add table lock UNLOCK TABLES; release label II, MyISAM locking mechanism 1, basic description
There are two modes of MySQL table-level locks: shared read locks (Read-Lock) and exclusive write locks (Write-Lock). Read operations on MyISAM tables do not block read requests from other threads on the same table, but block write requests to the same table; write operations on MyISAM tables block read and write operations on the same table by other threads; serialization occurs between read and write operations of the MyISAM engine, and between write and write operations. When a session thread acquires a write lock for a table, only the session thread that currently holds the lock can operate on the table. The read and write operations of other threads wait until the lock is released.
2. Validate the case
Based on the characteristics of the table locking mechanism above, the following two cases are used to verify.
Basic table structure CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'id', `user_ name` varchar (20) DEFAULT NULL COMMENT' username', `tell_ phone` varchar (20) DEFAULT NULL COMMENT 'mobile number', PRIMARY KEY (`id`) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=' user table' CREATE TABLE `dc_user_ info` (`city` int (11) NOT NULL COMMENT 'user ID', `city` varchar (20) DEFAULT NULL COMMENT' city', `city` varchar (20) DEFAULT NULL COMMENT 'country', PRIMARY KEY (`city`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=' user information table'; shared read lock
Session window one
-- 1, read lock LOCK TABLE dc_user READ;-- 2, current session query, OKSELECT * FROM dc_user;-- 4, current session write, ErrorINSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');-- 6, query other tables, ErrorSELECT * FROM dc_user_info;-- 7, release lock UNLOCK TABLES
Session window two
-- 3. Other session queries, OKSELECT * FROM dc_user;-- 5. Other session writes, ErrorINSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');-- 8. Perform write reads again, OKINSERT INTO dc_user (user_name,tell_phone) VALUES (' lock01','13267788998'); SELECT * FROM dc_user
The shared read mechanism of the table lock is verified here.
Exclusive write lock
The exclusive write mechanism of the table lock is verified here.
Query lock contention
View the configuration through the following statement
Show status like 'table%'
The higher the value of Table_locks_waited, the more serious the lock contention and the lower the efficiency.
3. Concurrent write problem
According to the test case of exclusive write lock, the MyISAM table also supports the concurrent execution of query and insert operations under certain conditions. Concurrent writes can be achieved by configuring the value of the system variable concurrent_insert [0recover1j 2].
Concurrent_insert=0, forbids concurrent writing; concurrent_insert=1, the default configuration AUTO, there is no hole in the MyISAM table, that is, there are no deleted rows in the middle of the table, for example, [1jie 2jue 3], after deletion of 2, another thread is allowed to write records from the end of the table while reading the table. Concurrent_insert=2, with or without holes in the MyISAM table, allows records to be inserted concurrently at the end of the table.
In the following example, session_1 acquires a READ LOCAL lock on a table that can query the table but cannot update the table; other threads (session_2), although they cannot delete and update the table, can insert the table concurrently, assuming that there are no holes in the middle of the table. 4. Priority problem
The read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. However, when a read operation and a write operation are requested at the same time, the write data will be given priority. This mechanism can be modified by configuration to specify the configuration parameter low-priority-updates, so that the MyISAM engine gives priority to the read request by default.
By executing the command SET
LOW_PRIORITY_UPDATES=1 to lower the write priority of the session. Lower the priority of the statement by specifying the LOW_PRIORITY property of the INSERT, UPDATE, and DELETE statement. 5. Application of table lock
The problem of data consistency verification, such as sales volume + remaining inventory = total goods, should lock both the order table and the inventory table in one session, so that the inventory table will not be modified when reading the order table. resulting in data errors.
3. InnoDB lock mechanism 1. Transaction basic concept transaction concept
A transaction is a series of operations (SQL statements) that are performed as a single logical unit of work. Either all of these operations were successful or none were successful.
Transaction characteristics ACID
Atomicity: multiple operations in a transaction either succeed or fail
Consistency: consistency of data integrity before and after the execution of a transaction
Isolation: during transaction execution, it should not be disturbed by other transactions.
Durability: once the transaction ends, the data is persisted to the database
Transaction problem
Dirty reading: one transaction reads data that is not committed by another transaction
Non-repeatable read: read the same data many times before and after a transaction, the data content is inconsistent, update scenario problem
Virtual reading (phantom reading): read many times before and after a transaction, the total amount of data is inconsistent, insert scenario problem
Isolation level
Read uncommitted: a transaction can read data from another uncommitted transaction.
Read committed: a transaction waits for another transaction to commit before it can read data and solve dirty reads.
Repeatable read: when starting to read data, the transaction is open, and modification operations are no longer allowed. Solution: dirty reading, non-repeatable reading.
Serializable: the highest transaction isolation level, transaction serialization sequential execution, to solve dirty reading, non-repeatable reading, phantom reading. But it is inefficient and consumes the performance of the database.
2. Description of locking mechanism
There are two biggest differences between InnoDB and MyISAM: one is to support transactional TRANSACTION, and the other is to use row-level locks. There are many differences between row-level locks and table-level locks. In addition, the introduction of transactions also brings new problems: concurrency, deadlocks and so on.
Shared lock: also known as read lock. Allows one transaction to read a row, preventing other transactions from acquiring exclusive locks for the same dataset. If transaction T adds a shared lock to the data object A, transaction T can read A but cannot modify A, and other transactions can only add a shared lock to An instead of a write lock until T releases the shared lock on A. This ensures that other transactions can read A, but no modifications can be made to A until T releases the S lock on A.
Exclusive lock: also known as write lock. Allows transaction update data with exclusive locks to be acquired, preventing other transactions from acquiring shared read locks and exclusive locks for the same resource. If transaction T adds a write lock to data object A, transaction T can read An or modify A, and other transactions can no longer add any locks to A until T releases the write lock on A.
3. Verify the basic table structure of the case: CREATE TABLE `int in01` (`id`int (11) DEFAULT NULL COMMENT 'id', `user_ name` varchar (20) DEFAULT NULL COMMENT' user name', `tell_ phone` varchar (20) DEFAULT NULL COMMENT 'phone number') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user Table' CREATE TABLE `int in02` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'id', `user_ name` varchar (20) DEFAULT NULL COMMENT' username', `tell_ phone` varchar (20) DEFAULT NULL COMMENT 'phone number', PRIMARY KEY (`id`) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT=' user Table'
Note the structure: the table dc_user_in01 primary key does not have an index. The table dc_user_in02 primary key has an index, but all use the INNODB storage engine, and the following verification cases will be different.
Unindexed structured table
Session window one
-- 1. Disable auto-commit SET AUTOCOMMIT = 0;-- 2, query id=1,OKSELECT * FROM dc_user_in01 WHERE id=1;-- 3, add write lock failed SELECT * FROM dc_user_in01 WHERE id=1 FOR UPDATE;-- 4, resume transaction commit SET AUTOCOMMIT = 1
Session window two
-- 1. Disable auto-commit SET AUTOCOMMIT= 0;-- 2, query id=2,OKSELECT * FROM dc_user_in01 WHERE id=2;-- 3, write failure (wait) INSERT INTO dc_user_in01 (id,user_name,tell_phone) VALUES;-- 4, write lock failure (wait) SELECT * FROM dc_user_in01 WHERE id=2 FOR UPDATE;-- 5, resume transaction commit SET AUTOCOMMIT=1; index structure table
Session window one
-- 1. Disable auto-commit SET AUTOCOMMIT = 0;-- 2, query id=1,OKSELECT * FROM dc_user_in02 WHERE id=1;-- 3, add write lock successfully SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE;-- execute here, then execute window 2mur4, resume transaction commit SET AUTOCOMMIT = 1
Session window two
-- 1. Disable automatic submission SET AUTOCOMMIT = 0;-- 2, query id=2,OKSELECT * FROM dc_user_in02 WHERE id=2;-- 3, query id=1,OK and add read lock SELECT * FROM dc_user_in02 WHERE id=1;-- 4, write successfully INSERT INTO dc_user_in02 (user_name,tell_phone) VALUES ('lock01','13267788998');-- 5. SELECT * FROM dc_user_in02 WHERE id=2 FOR UPDATE with id 2 -- 6. Failed to add write lock (wait), occupying SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE with id 1;-- 7. Restore transaction commit SET AUTOCOMMIT=1; Index invalidation
Here, we should pay attention to the problem of whether the index is used. In many queries, the index may not be executed for a variety of reasons.
Explain SELECT * FROM dc_user_in02 WHERE id=1; query lock contention show status like 'innodb_row_lock%'
The higher the values of Innodb_row_lock_waits and Innodb_row_lock_time_avg, the more serious the lock contention and the lower the efficiency.
4. Official documentation of Next-Key lock
To prevent misreading, InnoDB uses an algorithm called Next-Key locking, which combines record locking with gap locking, that is, when InnoDB performs row-level locks, it scans the index records and adds shared or exclusive locks to records that meet the index conditions.
[Next-Key] = [Record-lock] + [Gap-lock]
If the above lock mechanisms make people feel dark, then this Next-Key algorithm will make people doubt life.
Validate a case
The main purpose of this paper is to verify the existence mechanism of Gap-lock gap lock.
CREATE TABLE `dc_ gap` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'id', `id_ index` int (11) NOT NULL COMMENT' index', PRIMARY KEY (`id`), KEY `id_ index` (`id_ index`) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT=' gap table'; INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('1x,'2'); INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('3percent,' 4') INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('64th,' 7'); INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('84th,' 7'); INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('94th,' 9')
Session window one
-- 1. Start transaction START TRANSACTION;-- 3, lock two records of id_index=7 SELECT * FROM dc_gap WHERE id_index=7 FOR UPDATE;-- 9, commit COMMIT
Session window two
-- 2. Start transaction START TRANSACTION;-- 4, write wait, id_index=6INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('4questions,' 6');-- 5, write wait, id_index=4INSERT INTO `id_ gap` (`id`, `id_ index`) VALUES ('4records,' 4');-- 6, write successfully, id_index=3INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('4values,' 3') -- 7. Write wait, id_index=9INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('7percent,' 9');-- 8. Write successfully, id_index=10INSERT INTO `dc_ gap` (`id`, `id_ index`) VALUES ('7percent,' 10')
There is a gap between 7 and 4, and from 7 to 9, so the gap is locked [4jue 9] and contains the first and last values.
5. Basic description of Dead-Lock lock
Two or more transactions occupy each other on the same resource and request to lock the resources occupied by the other party, resulting in a dead loop, that is, deadlock.
Validate a case
Session window one
-- 1. Open transaction START TRANSACTION;-- 3, occupy id=6 resources SELECT * FROM dc_gap WHERE id=6 FOR UPDATE;-- 5. Occupy id=9 resources and wait for SELECT * FROM dc_gap WHERE id=9 FOR UPDATE
Session window two
-- 2. Open transaction START TRANSACTION;-- 4, occupy id=9 resources SELECT * FROM dc_gap WHERE id=9 FOR UPDATE;-- 6. Throw deadlocks SELECT * FROM dc_gap WHERE id=6 FOR UPDATE when occupying id=6 resources
A supplementary sentence: the database implements a variety of deadlock detection mechanisms, or the deadlock timeout waits for the end. After detecting the deadlock, the InnoDB storage engine will immediately return an error, otherwise the two transactions will look at each other for ten thousand years.
Note: deadlock in transactional business, can not be absolutely avoided, locking resources are few, fine-grained, try to avoid this situation.
Source code address GitHub address https://github.com/cicadasmile/mysql-data-baseGitEE address https://gitee.com/cicadasmile/mysql-data-base
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.