In layman's MySQL transaction processing and locking 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 transactional functionality.

2) Start the transaction using begin, use commit to end the transaction, and the middle can use rollback to rollback the transaction.

3) By default, the InnoDB table supports consistent reads.

The SQL standard defines 4 isolation levels: Read uncommited, read commited, repeatable read, serializable.

Read uncommited is dirty read, one transaction modifies a row, and another transaction can read to that row.

If the first transaction performs a rollback, then the second transaction reads a value that has never been formally seen.

Read commited is a consistent read that attempts to resolve dirty reads by reading only the submitted values, but this raises the issue of non-repeatable reads.

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

Repeatable read repeats the data rows that are locked when a transaction performs a read or write operation on the data rows.

But this approach raises the question of fantasy reading.

Because only rows that are read or written can be locked, and another transaction cannot be prevented from inserting data, later execution of the same query produces more results.

In serializable mode, the transaction is forced to execute sequentially. This is the default behavior recommended by the SQL standard.

4) If more than one transaction updates the same row, you can unlock the deadlock by rolling back one of the transactions.

5) MySQL allows you to set the isolation level using SET transaction.

6) transactions are only used for INSERT and UPDATE statements to update the data table and cannot be used for changes to the table structure. Executing a change table structure or begin commits the current transaction immediately.

7) Table-level locks are supported for all table types, but MyISAM only support table-level locks.

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

Read locks are shared locks, support concurrent reads, and write operations are locked.

Write locks are exclusive locks, while other threads cannot read tables or write tables during lockout.

8) If you want to support concurrent read and write, it is recommended to use the InnoDB table because it uses row-level locks to get more update performance.

9) Many times, it is possible to evaluate what kind of lock is more appropriate for an application through experience, but it is often difficult to say that a lock is better than the other, which depends on the application, and different places may require different locks. Currently MySQL has supported table-level locks for ISAM, MyISAM, MEMORY (HEAP) type tables, BDB tables support page-level locks, and InnoDB tables support row-level locks.

MySQL table-level locks are write-lock-first, and the queuing mechanism is used, so that there is no deadlock situation. For the InnoDB and BDB storage engines, deadlocks can be generated. This is because InnoDB automatically captures row locks, and BDB captures page locks when executing SQL statements, rather than doing so at the beginning of a transaction.

1.2. The advantages and disadvantages of different locks and selection

The advantages and options of row-level locks:

1) Reduce conflict locks when many threads request different records.

2) Reduce change data when transaction rollback.

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

Disadvantages of row-level locks:

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

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

3) When it is necessary to do GROUP by operations on most data frequently or to scan the entire table frequently, it is significantly worse than other locks.

4) with higher-level locks, it is more convenient to support a variety of different types of applications, because the cost of the lock is much smaller than the row-level lock.

5) You can replace row-level locks with application-level locks, such as Get_lock () and Release_lock () in MySQL. But they are advice locks (Original: These is advisory locks), so they can only be used in secure and trusted applications.

6) for InnoDB and BDB tables, MySQL only uses table-level locks when specifying lock TABLES. In both of these tables, it is recommended that you do not use lock TABLES because InnoDB automatically uses row-level locks and BDB uses page-level locks to ensure transaction isolation.

The advantages and the choice of table lock:

1) Many operations are read tables.

2) 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;

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

4) A lot of scan tables and GROUP by operations on the whole table, but no tables are written.

Disadvantages of Table Locks:

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

2) Other clients submit an UPDATE operation to the same table, and the client waits until the SELECT is complete before it can begin execution.

3) Other clients also submit a SELECT request to the same table. Because update has a higher priority than Select, select waits until the update is complete before starting execution, and it waits for the first select operation.

1.3. How to avoid the lock resource competition

1) make the SELECT speed as fast as possible, which may require creating some summary tables.

2) use parameter--low-priority-updates when starting mysqld. This causes the update operation to have a lower priority than SELECT.

In this case, in the above assumptions, the second select is executed before the INSERT, and there is no need to wait for the first select.

3) You can execute the SET low_priority_updates=1 command, specifying that all update operations are placed in a specified link to complete.

4) Use the Low_priority property to reduce the priority of INSERT, UPDATE, DELETE.

5) Use high_priority to increase the priority of the SELECT statement.

6) Starting with MySQL 3.23.7, you can specify the system variable Max_write_lock_count as a low value when starting mysqld, which forces the priority of all SELECT operations to temporarily increase the number of inserts for a table after reaching a specific value. It allows a READ lock after the WRITE lock reaches a certain number.

7) When a problem occurs with the INSERT and SELECT, you can instead use the MyISAM table, which supports concurrent SELECT and INSERT operations.

8) Insert DELAYED may be useful when there are both insert and delete operations on the same table.

9) The LIMIT parameter of delete may be useful when there is a problem with SELECT and delete.

10) using Sql_buffer_result while executing SELECT helps reduce the duration of the lock table.

11) You can modify the source code ' MYSYS/THR_LOCK.C ', using only one queue. In this case, the write-lock and read-lock priorities are the same, which may be helpful for some applications.

In layman's MySQL transaction processing and locking mechanism