MySQL (14) ---------- transaction processing, mysql ----------

MySQL (14) ---------- transaction processing, mysql ----------

The previous article's blog on transaction processing was not clearly written, difficult to read, and difficult to understand. So I have organized some documents to help me understand. Sorry!

 

MySQL transaction processing Learning

START TRANSACTION

COMMIT

ROLLBACK

Syntax

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, which is a permanent change of a change.

 

ROLLBACK can roll back the current transaction and cancel its change.

 

The set autocommit statement can be used to disable or enable the default autocommit mode for the current connection.

The optional WORK keywords are supported for COMMIT and RELEASE, and the CHAIN and RELEASE clauses.

 

CHAIN and RELEASE can be used to perform additional control on Transaction completion.

 

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

 

The and chain clause immediately starts a new transaction at the end of the current transaction, AND the new transaction has the same isolation level as the just-concluded transaction.

 

After the RELEASE clause terminates the current transaction, it will disconnect the server from the current client. Keywords containing NO can suppress CHAIN or RELEASE.

If the completion_type system variable is set to a certain value, the chain or release can be completed by default. In this case, the NO keyword is useful.

 

By default, MySQL runs in autocommit mode. This means that after you execute a statement for updating (modifying) The table, MySQL immediately stores the update to the disk.

 

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

Set autocommit = 0. After the AUTOCOMMIT variable is SET to zero and the autocommit mode is disabled, you must use COMMIT to store the changes to the disk, or if you want to ignore the changes made since the beginning of the transaction, use ROLLBACK.

 

If you want to disable the 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 summary = @ a where type = 1; COMMIT;

With start transaction, autocommit is still disabled until you end the TRANSACTION with COMMIT or ROLLBACK.

Then, the autocommit mode is restored to the original state.

 

BEGIN and begin work are supported as aliases of start transaction and used to initialize transactions.

Start transaction is a standard SQL syntax and a recommended method for starting an ad-hoc TRANSACTION.

The use of the BEGIN statement is different from that of the BEGIN keyword. The BEGIN keyword can start a BEGIN... END compound statement. The latter will not start a transaction.

 

You can also start a transaction as follows:

Start transaction with consistent snapshot;

The with consistent snapshot clause is used to start a CONSISTENT read for a storage engine WITH such features.

 

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

 

Starting a transaction will cause an implicit unlock tables to be executed.

To obtain the best results, transactions should only be executed using tables managed by a single transaction storage engine. Otherwise, the following problems may occur:

 

If the table you are using comes from multiple transaction-safe storage engines (such as InnoDB and BDB) and the transaction isolation level is not SERIALIZABLE, it is possible that when a transaction is committed, other ongoing transactions that use the same table will only be changed by the first transaction.

That is, the use of hybrid engines cannot guarantee the atomicity of transactions and may cause inconsistency. (If hybrid engine transactions are uncommon, you can use

Set transaction isolation level sets the isolation level to SERIALIZABLE .)

 

If you use a non-transactional Security table in a transaction, any changes to these tables are immediately stored, regardless of the status of the autocommit mode.

If you publish a ROLLBACK statement after updating a transaction table, an ER_WARNING_NOT_COMPLETE_ROLLBACK warning will appear.

Changes to the transaction security table are rolled back, but no changes to the non-Transaction Security table.

Each transaction is stored in a binary log in a group block, on the COMMIT. Rollback transactions are not included in logs. (Exception: Changes to non-transaction tables are not rolled back. If a rolled back transaction includes changes to non-transaction tables, the entire transaction uses a ROLLBACK statement at the end of the log to ensure that the changes to these tables are replicated .)

 

You can use set transaction isolation level to change the transaction isolation level.

 

Rollback can run slowly. You can also perform rollback (for example, when an error occurs) If you do not have specific requirements ). Therefore, when explicitly Rolling back to an implicit (rollback SQL command), SHOW PROCESSLIST will display Rolling back in the Stage column for 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 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.

 

 

Copyright Disclaimer: you are welcome to reprint it. I hope to add the original article address at the same time. Thank you for your cooperation and learning!