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

This article introduces the knowledge of "the advantages and disadvantages of Mysql row-level locks and page-level locks". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

If you want to do a lot of INSERT and SELECT operations on a table, but parallel inserts are not possible, you can insert records into the temporary table, and then periodically update the data in the temporary table to the actual table. You can do this with the following command:

Mysql

>

LOCK TABLES real_table WRITE, insert_table WRITE

Mysql

>

INSERT INTO real_table SELECT * FROM insert_table

Mysql

>

TRUNCATE TABLE insert_table

Mysql

>

UNLOCK TABLES

InnoDB uses Mysql 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.

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 GROUP BY 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:

UPDATE tbl_name SET

Column = value

WHERE

Unique_key_col = key_value

DELETE FROM tbl_name WHERE

Unique_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 GROUP BY 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.

That's all for "the pros and cons of Mysql row-level locks and page-level locks". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.