MySQL Detailed----------transaction processing

Preface: The previous article on the transaction processing of the post did not write clearly, read it is very obscure, difficult to understand, so there are some information to help understand, forgive me!

About MySQL transaction processing learning record

START TRANSACTION

COMMIT

ROLLBACK

Grammar

START TRANSACTION |

BEGIN [Work]

COMMIT [Work] [and [No] CHAIN] [[No] RELEASE]

ROLLBACK [Work] [and [No] CHAIN] [[No] RELEASE]

SET autocommit = {0 | 1}

The start transaction or BEGIN statement can start a new transaction.

Commit can commit the current transaction, and the change becomes a permanent change.

Rollback can roll back the current transaction and cancel its changes.

The SET autocommit statement can disable or enable the default autocommit mode for the current connection.

The optional work keyword is supported for commit and release, with the chain and release clauses.

Chain and release can be used for additional control over transaction completion.

The value of the COMPLETION_TYPE system variable determines the nature of the default completion.

The AND Chain clause starts a new transaction immediately at the end of the current transaction, and the new transaction has the same isolation level as the transaction that just ended.

The release clause causes the server to disconnect from the current client after terminating the current transaction. Contains no keyword can suppress chain or release complete.

No keyword is useful if the COMPLETION_TYPE system variable is set to a certain value so that interlock or release completion can be done by default.

By default, MySQL operates in autocommit mode. This means that when you execute a statement that updates (modifies) the table, MySQL immediately stores the update to disk.

If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB clusters), you can disable the autocommit mode by using the following statement:

SET autocommit=0; After you disable autocommit mode by setting the autocommit variable to zero, you must use commit to store the changes to disk, or use rollback if you want to ignore changes made since the transaction started.

If you want to disable autocommit mode for a single series of statements, you can use the START TRANSACTION statement:

START TRANSACTION;

SELECT @a:=sum (Salary) from table1 WHERE type=1;

UPDATE table2 SET [email protected] WHERE type=1; COMMIT;

Using start Transaction,autocommit is still disabled until you end the transaction with commit or rollback.

Then the autocommit mode reverts to its original state.

The Begin and begin work is supported as the alias of the start transaction and is used to initialize the transaction.

Start transaction is the standard SQL syntax and is the recommended way to start a ad-hoc transaction.

The BEGIN statement differs from the use of the BEGIN keyword. Begin keyword to start a BEGIN ... End Compound statement. The latter does not begin a transaction.

You can also start a transaction in the following ways:

START TRANSACTION with consistent SNAPSHOT;

The WITH consistent snapshot clause is used to initiate a consistent read for the storage engine that has such functionality.

Currently, this clause applies only to InnoDB. The effect of this clause is the same as publishing a start TRANSACTION, followed by a select from any InnoDB table.

Starting a transaction causes an implicit unlock tables to be executed.

For best results, transactions should only be performed using tables managed by a single transactional storage engine. Otherwise, the following issues occur:

If you are using a table from multiple transactional security storage engines (for example, InnoDB and BDB), and the transaction isolation level is not serializable, it is possible that when a transaction commits, other transactions that are in progress, using the same table, will only be changed by the first transaction.

That is, using a hybrid engine does not guarantee the atomicity of transactions and can cause inconsistencies. (If the mixed-engine transaction is not often available, you can use it as needed

Set TRANSACTION Isolation level sets the isolation class to serializable. ）

If you use non-transactional security tables in a transaction, any changes to those tables are immediately stored, regardless of the state of the autocommit mode.

If you publish a ROLLBACK statement after updating a transaction table in a transaction, a er_warning_not_complete_rollback warning appears.

Changes to the transaction-safe table were rolled back, but no changes were made to the non-transactional security table.

Each transaction is stored in a binary log in a group block, above the commit. Transactions that are rolled back are not counted into the log. (Exception: Changes to non-transactional tables are not rolled back.) If a transaction that is rolled back includes changes to a non-transactional table, the entire transaction is counted into the log using a ROLLBACK statement at the end to ensure that changes to these tables are replicated. ）

You can change the isolation level of a transaction using set TRANSACTION isolation levels.

Rollback can run at a slower speed. You can also roll back if the user does not explicitly require it (for example, when an error occurs). As a result, when you roll back explicitly and implicitly (ROLLBACK SQL commands), show processlist displays the rolling in the stage column for the connection.

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.

Copyright NOTICE: Welcome to reprint, hope to reprint the same time add the original address, thank you for your cooperation, learning happy!

MySQL Detailed----------transaction processing