InnoDB locks, transactions, and deadlocks.

There are 3 implementations of the InnoDB row lock

1.record lock, locked single-line record

2.gap Lock, our well-known gap lock, locks a range but does not contain the record itself

3.next-key Lock, the joint use of the first two locks, not only locks the range, but also locks the record itself.

InnoDB The default transaction isolation level is repeatable read, which, under this isolation level, is locked in a third way, and the transaction level is in Read committed with the first locking method.

Here is a brief talk about the problem of lock, do a cushion

1. The first problem, dirty read: Simply put, under different transactions, the current transaction can read to another transaction uncommitted data, which is dirty data. Dirty reads don't happen because it happens because the transaction isolation level is read uncommitted, and most of the databases are now read committed; but occasionally it happens in mysq Master-slave.

2. The second problem, non-repeatable READ: is a transaction within two times to read a different data, but the second read the data is already committed, so this is actually normal situation, in this case the transaction isolation level is read Committed. This leads to a more important question, in InnoDB, is to avoid non-repeatable reads by Next-key Lock (the third lock on top), which is phantom problem (phantom reading). Under Next-key Lock, the lock is not only the index, but also locks the scope of the index coverage, so there will be no data to be queried during the current transaction execution, another transaction change and submit the result. Nnodb The default transaction isolation level is repeatable read, using Next-key Lock avoids phantom read issues.

3. Third problem, missing update: The update operation of one transaction is overwritten by the update operation of another transaction, which in theory will not happen now, because even the weakest isolation level of READ UNCOMMITTED will be the number of updates to the data plus the lock, So this is not the case at the database level, but this problem occurs at the business level, such as bank transfers, and it is best to have the transaction serialized out to avoid this problem. In fact, our daily use of the database operation language, select, UPDATE, delete we are not very deep to understand the beginning to use, so to a large extent, the database to ensure the security of data, but the programmer will make a lot of mistakes, so my feeling is that the database is really profound ah, Seemingly simple things, such as the SELECT statement, have a deep connotation, do not understand its principle, there is a problem, all face confused force.

Talk about deadlocks and lock escalation

InnoDB processing deadlocks in two ways, one is time-out, one is a timeout graph, the former is the passive detection of deadlocks, the latter is the active detection, InnoDB for the general exception will not be rolled back, but the deadlock will be rolled back, if a deadlock occurs, or if a deadlock is detected imminent (time-out graph), Will proactively roll back a transaction, allowing other things to go on.

Lock escalation is to make the current lock granularity coarse, if the lock is considered a rare resource (MS SQL Server, to create a lock object for each record), in accordance with this idea, the design of the database may occur when multiple row locks are promoted to page lock or table lock situation, in which case, the protection of system resources, Avoid excessive system resources that are used to maintain locks, which increases efficiency.

InnoDB does not have a lock escalation problem because it does not produce row locks on a per-record basis, but instead manages locks on a per-page basis for transaction access, using bitmaps, so the overhead is the same regardless of whether one thing locks a record or multiple records in a page.

Consider that if there are 1000 rows of data, SQL Server will generate a lock object for each row, and InnoDB will only produce a lock object on the data page (a data page is sure to hold multiple Row Records), in which case the INNODB implementation of the lock resource cost will be quite small, So SQL Server will have the concept of lock escalation.

Finally, say a few more, said before, SQL Server for foreign keys to manually indexed, and InnoDB will be actively indexed, and delete this index is not allowed, which will greatly reduce the chances of InnoDB deadlock.

In fact, the top of the database because of the standard, so we use different databases, such as Oracle, MySQL, SQL Server, the feeling seems to be the same, but only in-depth implementation, will find that each manufacturer's thinking is really different, so the product in various cases of performance will have a variety of differences.

OK, about InnoDB Lock and deadlock this piece really is profound, I also just learned a little fur, still need in-depth study, this is a study note, do not like to spray, welcome correction, common progress.