MySQL transaction processing and lock mechanism

MySQL transaction processing and lock mechanism

1. Transaction Processing and concurrency

1.1. Basic Knowledge and related concepts

1) All table types can use locks, but only InnoDB and BDB have built-in transaction functions.

2) use begin to start the transaction and commit to end the transaction. You can use rollback to roll back the transaction in the middle.

3) by default, InnoDB tables support consistent read.

The SQL standard defines four isolation levels: read uncommited, read commited, repeatable read, and serializable.

Read uncommited is dirty read. One Transaction modifies one row, and the other transaction can also read this row.

If the first transaction executes rollback, the second transaction reads the value that has never been formally displayed. ?

Read commited is consistent read. It tries to solve the dirty read problem by only reading the committed value, but this causes the problem of non-repeated read.

A transaction executes a query and reads a large number of data rows. Before reading the data, another transaction may have changed the data row. When the first transaction tries to execute the same query again, the server returns different results.

Repeatable read can be used to read data rows repeatedly. These data rows are locked when a transaction reads or writes data rows.

However, this method raises the issue of fantasy reading.

Because only the read or write rows can be locked, another transaction cannot be prevented from inserting data. Later, executing the same query will produce more results.

In serializable mode, transactions are executed in sequence. This is the default behavior recommended by the SQL standard.

4) if multiple transactions update the same row, you can roll back one of the transactions to release the deadlock.

5) MySQL allows the use of set transaction to set the isolation level.

6) transactions are only used to update data tables using insert and update statements, and cannot be used to change the table structure. If you execute a change table structure or begin, the current transaction is committed immediately.

7) All table types support table-level locks, but MyISAM only supports table-level locks.

8) There are two types of table-level locks: Read locks and write locks.

Read locks are shared locks that support concurrent reads and lock write operations.

The write lock is an exclusive lock. During the lock, other threads cannot read or write tables.

8) to support concurrent read/write, we recommend that you use the InnoDB table because it uses row-level locks to achieve more update performance.

9) In many cases, experience can be used to evaluate what kind of lock is more suitable for the application. However, it is often difficult to say that a lock is better than other locks. It is determined by the application, different locks may be required in different places. Currently, MySQL supports table-level locks for ISAM, MyISAM, and MEMORY (HEAP) tables. BDB tables support page-level locks and InnoDB tables support row-level locks.

10) the table-level locks of MySQL give priority to write locks and adopt the queuing mechanism, so no deadlock will occur. For InnoDB and BDB storage engines, deadlocks may occur. This is because InnoDB will automatically capture row locks and BDB will capture page locks when executing SQL statements, rather than doing so at the beginning of the transaction.

1.2. Advantages and disadvantages of different locks and their selection

Advantages and choices of Row-level locks:

1) Reduce the conflict lock when multiple threads request different records.

2) reduce data changes during transaction rollback.

3) it is possible to lock a single row of records for a long time.

Disadvantages of Row-Level Lock:

1) more memory is consumed than page-level locks and table-level locks.

2) when used in a large number of tables, it is slower than page-level locks and table-level locks because they need to request more resources.

3) When You Need To perform group by operations on most data frequently or scan the entire table frequently, it is obviously worse than other locks.

4) using higher-level locks makes it easier to support different types of applications, because the cost of such locks is much lower than that of Row-level locks.

5) application-level locks can be used to replace row-level locks, such as GET_LOCK () and RELEASE_LOCK () in MySQL (). However, they are suggested locks (Original: These are advisory locks), so they can only be used in secure and trusted applications.

6) for InnoDB and bdb tables, MySQL uses table-level locks only when the lock tables Table is specified. We recommend that you do not use lock tables in these two TABLES, because InnoDB automatically uses row-level locks and BDB uses page-level locks to ensure transaction isolation.

Advantages and options of table lock:

1) many operations are read tables.

2) read and UPDATE indexes with strict conditions. When an UPDATE or deletion can be obtained using 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;

3) SELECT and INSERT statements are executed concurrently, but there are only a few UPDATE and DELETE statements.

4) Many scan tables and group by operations on the entire table, but no write table exists.

Disadvantages of table lock:

1) A client submits a SELECT operation that requires a long time to run.

2) When other clients submit the UPDATE operation for the same table, the client must wait until the SELECT operation is complete before execution can begin.

3) Other clients submit SELECT requests for the same table. Since the priority of UPDATE is higher than that of SELECT, the SELECT statement is executed only after the UPDATE is complete. It is also waiting for the first SELECT operation.

1.3. How to avoid lock Resource Competition

1) to make the SELECT statement as fast as possible, you may need to create some summary tables.

2) When starting mysqld, use the -- low-priority-updates parameter. Therefore, the priority of the update operation is lower than that of the SELECT operation.

In this case, in the preceding assumptions, the second SELECT statement will be executed before the INSERT statement, and you do not need to wait for the first SELECT statement.

3) You can run the SET LOW_PRIORITY_UPDATES = 1 command to specify that all update operations are put in a specified link.

4) use the LOW_PRIORITY attribute to reduce the priority of INSERT, UPDATE, and DELETE.

5) HIGH_PRIORITY is used to increase the SELECT statement priority.

6) starting from MySQL 3.23.7, you can specify the system variable max_write_lock_count as a relatively low value when starting mysqld, it can temporarily increase the insert count of a table to the priority of all SELECT operations after a specific value. It allows a READ lock after the WRITE lock reaches a certain number.

7) When the INSERT and SELECT statements are used together, you can use the MyISAM table instead. It supports concurrent SELECT and INSERT operations.

8) insert delayed may be useful when both INSERT and delete operations are performed on the same table.

9) when the use of SELECT and DELETE fails, the LIMIT parameter of DELETE may be very useful.

10) using SQL _BUFFER_RESULT when executing the SELECT statement helps reduce the duration of the short lock table.

11) You can modify the source code 'mysys/thr_lock.c 'and use only one queue. In this case, the write lock and read lock have the same priority, which may be helpful to some applications.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: