Shulou(Shulou.com)06/01 Report--

The following mainly brings you the difference and usage of MySQL and Oracle line locks. I hope these contents can bring you practical use, which is also the main purpose of this article to edit the difference and usage of MySQL and Oracle line locks. All right, don't talk too much nonsense, let's just read the following.

InnoDB

The INNODB table is a table organized by the index, the primary key is a clustered index, and the non-primary key index contains primary key information.

INNODB defaults to row locks.

INNODB row locks are achieved by locking index items, that is, InnoDB uses row-level locks only if data is retrieved through index conditions, otherwise table locks are used.

InnoDB Row Lock implementation

InnoDB row locking is achieved by locking the index item on the index, and if there is no index, InnoDB locks the record through a hidden clustered index. InnoDB row locks are divided into three situations.

Record lock: locks the index entry.

Gap lock: locks the gap between index items, the gap before the first record, or the gap after the last record.

Next-key lock: a combination of the first two to lock the record and the gap in front of it.

The row lock implementation feature of InnoDB means that if the data is not retrieved through the index condition, InnoDB will lock all records in the table, the actual effect is the same as the table lock!

In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may lead to a large number of lock conflicts.

Oracle

On each row of data in Oracle, there is a flag bit to indicate whether the row of data is locked. This greatly reduces the maintenance overhead of row-level locks. Once the lock flag on the data row is set, it indicates that the data is locked by X, and Oracle does not have an S lock on the data row.

Many technicians who don't know much about Oracle may think that each TX lock represents a blocked row of data, but it's not. The original meaning of TX is Transaction (transaction), when a transaction performs data changes (Insert, Update, Delete) for the first time or uses SELECT. When the FOR UPDATE statement makes a query, it acquires a TX (transaction) lock that is not released until the transaction ends (performing a COMMIT or ROLLBACK operation). Therefore, a TX lock can correspond to multiple rows of data locked by the transaction.

The row-level lock of ORACLE is only the ITL of the data block and the LB lock identification bit of the data header, so it does not need to consume additional resources. It is important to note that transactions are not blocked by rows, but by other transactions. So some databases have a lock upgrade mechanism, while ORACLE does not. The row-level lock of ORACLE is only the ITL of the data block and the LB lock identification bit of the data header, so it does not need to consume additional resources.

For the above about the difference and usage of MySQL and Oracle row locks, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.