(GO) MySQL Lock related knowledge

Original address: http://www.cnblogs.com/RicCC/archive/2009/09/25/mysql.html

Storage Engine

Attribute	MyISAM	Heap	BDB	InnoDB
Transactions	No	No	Yes	Yes
Lock Granularity	Table	Table	Page (8 KB)	Row
Storage	Split files	In-memory	Single File per table	Tablespace (s)
Isolation Levels	None	None	Read committed	All
Portable format	Yes	N/A	No	Yes
Referential integrity	No	No	No	Yes
Primary Key with Data	No	No	Yes	Yes
MySQL caches data records	No	Yes	Yes	Yes
Availability	All versions	All versions	Mysql-max	All Versions

To make it easier to follow the unique characteristics of each storage engine, I created this Magic Quadrant diagram:

Below is some examples of using the best storage engine for different tasks:
Search Engine-ndbcluster
Web stats Logging-flat file for the logging with a offline processing demon processing and writing all stats into Innod B tables.
Financial Transactions-innodb
Session Data-myisam or Ndbcluster
Localized calculations-heap
Dictionary-myisam

MyISAM
Designed to deal with reading frequency is much more than write frequency, the query performance is very good; no transaction support, no redo, undo log, support table lock;
Each table uses a separate file, the index and data are stored in different files, and the storage of the files is stored as File Block (page). Only the index is cached, the actual data is not cached, the disk IO is used each time the data is read, and the index is organized in memory as cache block, corresponding to the file block. The cached index is managed using the LRU algorithm, and in order to increase the utilization of the cache, it is supported to divide the cache into areas such as hot zone, Warm area
Key_buffer_size: Set Total cache size
Key_buffer_block_size: Setting the cache block size
Key_cache_division_limit: Divides the entire buffer into multiple regions as a percentage. The system defaults to 100, which is only warm area
Key_cache_age_threshold: Controls when the area is degraded, the smaller the value, the easier it is to downgrade to the next level of areas
Table scan is divided into sequential scan and Radom scan 2 ways, read_buffer_size set sequential scan when using the cache, read_rnd_buffer_size settings radom Cache used when scan
InnoDB
Designed for high concurrency and read-write situations, support row lock (must have index support), support transaction security, with redo, undo log, with fault recovery capability, its transaction implementation of the SQL92 4 levels, support foreign keys, to achieve the database referential integrity characteristics;
Adaptive Hash Index:innodb automatically detects index status, if found to improve efficiency through the hash index, a b-tree-based hash index is created internally and adjusted automatically based on changes in the B-tree index. The hash index is not created based on the entire b-tree, only for one part of it, and is not stored to disk, only created in the cache
InnoDB data files support shared tablespace and exclusive tablespace 2 modes, where data and indexes are stored together to support caching of data and indexes. Storage structure from large to small tablespace->segment->extent->page,page default to 16KB, each extent contains 64 Page, each segment store the same data, In general, each table is stored in a separate segment

lock Mechanism
There are three kinds of table lock (MyISAM), page lock (BDB), row lock (InnoDB)

table Lock：
There are 4 queue record lock usage: current Read-lock (currently read lock queue), Pending Read-lock (queue waiting for read lock), current Write-lock (currently write lock queue), Pending Write-lock (queue waiting for write lock)
read lock, write lock：
a). The write lock of the current resource in the present Write-lock blocks read and write lock requests.
b). Write locks in the write type in Pending write-lock block all read lock requests except for the read_high_priority type, and read locks of the read_high_priority type have a higher precedence than write-type locks. As a result, it blocks all write lock requests in Pending Write-lock, except for write-type writes, other types of write-lock precedence in Pending Write-lock are lower than read locks (increasing response time for queries)
c) in current Write-lock, all read and write lock requests except write_only are allowed when the write-lock type for the resource is Write_allow_write
MyISAM is the official MySQL storage engine, the full use of MySQL's own table lock mechanism, several other support transactions of the storage engine is to let MySQL lock processing to the storage engine self-implementation, they only hold the write_allow_write type of lock in MySQL, As for lock definition, concurrency conflict control, etc. are handled by each storage engine
MyISAM table lock Optimization Tips: MyISAM table Lock Read-write blocking, write lock priority higher than read lock
a). parameter options low_priority_updates setting write lock priority is lower than read lock to ensure query response speed
b). parameter options Concurrent_insert Configure whether to use concurrent Insert attribute, can implement concurrent read and insert operation, configuration value: 0: Concurrent insert is not allowed; 1: When there is no free space in the data file, it can be inserted concurrently at the end of the file. 2: Allows concurrent insertions at the end of a file regardless of whether the data file has free space (the insert operation will continue at the end of the file and the free space in the middle is unusable for tables with very few deletions)

InnoDB of the line lock：
is not the MySQL implementation of the lock mechanism, row locks are implemented by other storage engines, here in InnoDB for example (different storage engine implementation mechanism is not the same)
An Oracle's Row lock records the lock information in the transaction slot of the physical block, while InnoDB records the lock information (Gap Lock) at the beginning and end of the index key value, so the InnoDB row lock is just a range lock implemented by the index, and the index can be used to navigate to the data row
Lock type and exclusivity:

Intent

	shared Lock (S)	exclusive Lock (X)	Shared Lock (IS)	Intent Exclusive Lock (IX)
Shared Lock (S)	Compatible	Conflict	Compatible	Conflict
Exclusive Lock (X)	Conflict	Conflict	Conflict	Conflict
Intent shared lock (IS)	Compatible	Conflict	Compatible	Compatible
Intent exclusive Lock (IX)	Conflict	Conflict	Compatible	Compatible

InnoDB potential problems with line locks:
a). If the index information cannot be used, INNODB will use the table lock
b). When the index is not determined to a row of data, InnoDB locks the data in the entire range to which the index matches. For example, using an index to navigate to 10 records, plus the non-indexed condition can be accurately determined to a record, in which case the InnoDB still locks the 10 records
Transaction ISOLATION Level:
INNODB implements 4 Isolation levels for SQL92: Read uncommited, read commited, repeatable read, Serializable
The INNODB has a deadlock detection mechanism that will be a deadlock victim for the smaller of the 2 transactions in which the deadlock occurs (with a small amount of modified data). Of course only within the scope of the InnoDB storage engine, deadlocks across the storage engine can only be handled through the deadlock timeout setting

Index
There are 4 main types of MySQL indexes: B-tree index, hash index, fulltext index, R-tree index

B-tree Index: Universal Index Type
The B-tree index in InnoDB is divided into cluster form of Primary key and secondary index, similar to SQL Server, where the leaf nodes of the Primary key index are actual data files, sorted in indexed order, secondary The leaf node of index stores only the primary key value. MyISAM's primary key index and non-primary key index are no different, similar to InnoDB's secondary index, except that its leaf node is not the PK value, but the information that navigates directly to the data row.

Hash Index:
The index key value of the data is indexed by the hash operation, and the query condition is also hashed when the query matches, and the hash value is compared. Mostly memory and NDB cluster storage engine use
Hash index query efficiency is very high, because do not need to like b-tree from the root to the page node ("MySQL performance Tuning and architecture design" said the hash index can be located at a time to locate the record, this argument may be problematic, the organization of the hash index, Hash value matching also requires data structure and algorithm implementation, it is not possible to locate the hash index in a b-tree way to organize, better than the B-tree index may be its b-tree data volume will be small, even if this may mean the existence of hash conflict, The argument that its efficiency is higher than the B-tree index is still to be verified and may require a lot of preconditions.
Disadvantages: equivalent matching only; Hash indexes are unordered, may require additional sorting operations, cannot be partially matched, can only be matched by full index, and may be less efficient when hash conflicts are encountered

Fulltext Index: Only MyISAM char, VARCHAR, text three data types support Fulltext index, mainly used to optimize the less efficient like '%***% ' operation
MySQL Fulltext Chinese support remains to be seen, Fulltext index creation cost is higher

R-tree Index: For spatial data retrieval
MySQL has spatial data type geometry (5.0.16 before MyISAM support, then BDB, InnoDB, NDB Cluster, Archieve, etc.), only MyISAM storage engine supports R-tree index

(GO) MySQL Lock related knowledge