MySQL specific explanation (+)----------transaction processing

In the previous article, the blog post on transaction processing has not been written clearly and is very obscure to read. Very difficult to understand, so there are some information to help understand. Excuse 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 is able to commit the current transaction. Change is a permanent change.

Rollback is able to 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 the completion of a transaction.

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

The and chain clauses are at the end of the current transaction. Start a new transaction immediately. And the new transaction has the same isolation level as the transaction that just ended.

The release clause terminates after the current transaction. Causes the server to disconnect from the current client. Including no keyword can suppress chain or release complete.

Suppose the Completion_type system variable is set to a certain value. So that the chain or release can be done by default, at this time no keyword practical.

By default, MySQL is implemented in autocommit mode. This means that. After you execute a statement that updates (changes) the table. MySQL immediately stores the update to disk.

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

SET autocommit=0; By setting the autocommit variable to zero. After disabling autocommit mode. You must use commit to store the changes to disk. Or assume that you want to ignore changes made since the transaction started. Use rollback.

Suppose 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 it's a 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 start a transaction.

You can also start a transaction in such a way as the following:

START TRANSACTION with consistent SNAPSHOT;

The WITH consistent snapshot clause is used to initiate a consistent read. Used for storage engines that have such features.

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

Starting a transaction causes an implied unlock tables to be run.

For best results, transactions should only be run with tables managed by a single transactional storage engine.

Otherwise, the following problem occurs:

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

That is The use of a hybrid engine does not guarantee the atomicity of transactions. and will cause inconsistencies. (assuming that mixed-engine transactions are not always available, you can use

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

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

Assume that you have updated a transaction table in a transaction. When you publish a rollback statement, 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 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.) Assuming that a transaction that is rolled back contains 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 be performed at a slower speed. Rollback is also possible when the user does not understand the requirements (e.g.. When an error occurs). Therefore, when it is rolled back clearly and implicitly (ROLLBACK SQL commands). 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 are able to use locks. But only InnoDB and BDB have built-in transactional functionality.

2) Start the transaction using begin, and use commit to end the transaction. The middle is able to roll back the transaction using rollback.

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

There are 4 isolation levels defined in the SQL standard: Read uncommited. Read commited, repeatable read. Serializable.

Read uncommited is dirty reading. A transaction has changed one row. There is also a transaction that can read the row.

Assuming that the first transaction runs a rollback, the second transaction reads a value that has never been formally seen.

?

Read commited is consistent. Attempts to solve the problem of dirty reads by simply reading the value of the commit, but this raises the issue of non-repeatable reading.

A transaction runs a query. A large number of data rows were read.

Before it finishes reading, there is another transaction that may have completed a change to the data row. When the first transaction tries to run the same query again, the server returns different results.

Repeatable read can be repeated. These data rows are locked when a transaction runs a read or write operation on a data row.

But such a way raises the question of fantasy reading.

Because only the rows that are read or written can be locked, there is no way to prevent another transaction from inserting data, and later running the same query produces many other results.

In serializable mode, transactions are forced to run sequentially. This is the default behavior recommended by the SQL standard.

4) Assume that multiple transactions update the same row. It is possible to unlock a deadlock by rolling back one of the transactions.

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

6) transactions are only used for INSERT and UPDATE statements to update data tables. cannot be used for changes to the table structure. Running a change table structure or begin commits the current transaction 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.

A read lock is a shared lock. Supports concurrent reads. The write operation is locked.

A write lock is an exclusive lock that cannot be read or written by other threads during a lock.

8) If you want to support concurrent read and write, it is recommended to use the InnoDB table, because it is a row-level lock, can get a lot of other update performance.

9) Very often, it is more appropriate to evaluate what kind of lock-in application through experience. Just usually very hard to say that a lock is better than the other. It all depends on the application. Different places may require different locks.

Currently MySQL has support for the ISAM, MyISAM, MEMORY (HEAP) Type table of table-level locks. The BDB table supports page-level locks, and the InnoDB table supports row-level locks.

MySQL table-level lock is a write-lock priority, and is the use of queuing mechanism, so that there is no deadlock situation. For the InnoDB and BDB storage engines, deadlocks can be generated. This is because InnoDB will capture the row lock on its own initiative. BDB will capture the page lock when the SQL statement is run. Rather than doing it at the beginning of the transaction.

1.2. The advantages and disadvantages of different locks and selection

The strengths and choices of row-level locks:

1) Reduce conflict locks when very 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 a lot of other memory than page-level locks and table-level locks.

2) when used in a large number of tables. Slower than page-level and table-level locks. Because he needs to ask for a lot of other resources.

3) When you need to do GROUP by operations on most data frequently or when you need to scan the entire table frequently. Is significantly worse than the other locks.

4) with higher-level locks, it is more convenient to support a variety of different types of applications, because such locks are much less expensive than row-level locks.

5) Ability to 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. As the InnoDB themselves take the initiative to adopt row-level locks. BDB uses page-level locks to ensure the isolation of transactions.

The advantages and the choice of table lock:

1) Very many operations are read tables.

2) Read and update on a strictly conditional index. When update or delete 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 run concurrently, but there are only very few UPDATE and DELETE statements.

4) Very many scan tables and GROUP by operations on the whole table, but no matter what the table is written.

Disadvantages of Table Locks:

1) A client submits a SELECT operation that needs to be executed for a long time.

2) The other client submits an UPDATE operation to the same table. The client will have to wait until the SELECT is finished and the talent starts to run.

3) The other client also submits a SELECT request to the same table. Because UPDATE has a higher priority than SELECT. So SELECT will wait until the UPDATE is complete before it starts running, and it is waiting for the first select operation.

1.3. How to avoid lock resource competition

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

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

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

3) The ability to run 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 INSERT. UPDATE. The priority of the 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 to a lower value when starting mysqld. It can force the priority of all SELECT operations to temporarily increase the number of inserts for a table after reaching a specific value. It agrees to have a READ lock after the WRITE lock reaches a certain number.

7) when the INSERT and SELECT are used together in the event of a failure. The ability to switch to the MyISAM table, which supports concurrent SELECT and INSERT operations.

8) When there is an insert and delete operation at the same time on the same table. INSERT DELAYED can be very useful.

9) When the SELECT and delete are used together, the LIMIT parameters of the delete may be very useful.

10) using Sql_buffer_result when running SELECT helps reduce the duration of the lock table.

11) Be able to change the source ' mysys/thr_lock.c ', using only one queue. In this case, the write-lock and read-lock priority is the same. This may be helpful for some applications.

MySQL specific explanation (+)----------transaction processing