Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve row-level lock in MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

How to implement MySQL Row level Lock

Mysql > LOCKTABLESreal_tableWRITE,insert_tableWRITE

Mysql > INSERTINTOreal_tableSELECT*FROMinsert_table

Mysql > TRUNCATETABLEinsert_table

Mysql > UNLOCKTABLES

InnoDB uses row-level locks and BDB uses page-level locks. For InnoDB and BDB storage engines, deadlocks can occur. This is because InnoDB automatically catches row locks, and BDB captures page locks when the SQL statement is executed, not at the beginning of the transaction.

What are the advantages and disadvantages of MySQL row-level locks

The advantages of MySQL row-level locks are:

Reduce conflicting locks when many threads request different records.

Reduce the change of data when the transaction is rolled back.

Makes it possible to lock individual rows of records for a long time.

The disadvantages of MySQL row-level locks are:

Consumes more memory than page-level and table-level locks.

When used in large tables, it is slower than page-level locks and table-level locks because it needs to request more resources.

When most of the data needs to be GROUPBY frequently or the entire table needs to be scanned frequently, it is obviously worse than other locks.

Using higher-level locks makes it easier to support different types of applications because such locks are much less expensive than row-level locks.

Table-level locks are superior to page-level and row-level locks in the following situations:

A lot of operations are meter reading.

Read and update on a strictly conditional index, when updates or deletions can be read with a separate index:

UPDATEtbl_nameSETcolumn=valueWHEREunique_key_col=key_value

DELETEFROMtbl_nameWHEREunique_key_col=key_value

SELECT and INSERT statements are executed concurrently, but there are very few UPDATE and DELETE statements.

A lot of scanning tables and GROUPBY operations on the whole table, but there is no writing to the table.

The difference between table-level locks and row-level or page-level locks is also:

Versions will be made with one write and multiple reads at the same time (such as concurrent inserts in MySQL). That is, the database / table supports a variety of attempts depending on the point in time when you start accessing the data. Other names are: time schedule, write copy, or copy on demand.

On how to achieve row-level locks in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.