Study and organize MySQL transactions

Source: Internet
Author: User
Tags mysql command line

 

Transactions are one of the important features of a database that distinguishes a file system. In a file system, if you are writing a file but the operating system suddenly crashes, the file may be damaged, of course, there will also be some mechanisms to restore files to a certain point in time, such as relying on the original backup.

 

1. The purpose of introducing transactions

The main purpose of introducing transactions to the database system is to convert the database from one consistent state to another. When submitting a database job, you can ensure that either all the modifications have been saved or all the modifications are not saved.

 

2 ACID Introduction

Transactions in the InnoDB Storage engine fully comply with ACID features. ACID is as follows:

Atomicity means that the entire transaction is successful only when all database operations in the transaction are successfully executed. Only one

The integrity constraints of the database are not damaged before and after the transaction starts.

Database

 

3. Transaction implementation

Isolation is achieved through locks. The other three atomicity, consistency, and persistence durability are achieved through database redo and undo.

 

3.1 redo

In the Innodb Storage engine, transaction logs are implemented through the log Buffer (InnoDBLog Buffer) from the redo day to the file and the Innodb Storage engine. When a transaction starts, an LSN (Log Sequence Number Log serial Number) of the transaction is recorded. when the transaction is executed, the transaction Log is inserted into the Log buffer of the InnoDB Storage engine; when a transaction is committed, the log buffer of the InnoDB Storage engine must be written to disk (default implementation: innodb_flush_log_at_trx_commit = 1 ). That is, before writing data, you need to Write logs first. This method is called the pre-Write LOG method WAL (Write-Ahead Logging ).

The InnoDB Storage engine ensures transaction integrity by writing logs. This means that the data pages stored on the disk and the pages in the memory buffer pool are not synchronized. for changes to pages in the memory buffer pool, the redo log file is first written and then written to the disk, therefore, it is an Asynchronous Method. You can use show engine innodb status; to observe the gap between the current disk and log:

Create table z (a int, primary key (a) engine = innodb;

 

DELIMITER $

Use test $

Drop procedure if exists 'test'. 'Load _ test' $

Create procedure load_test (count int)

BEGIN

DECLARE I INT UNSIGNED DEFAULT 0;

Start transaction;

WHILE I <COUNT DO

REPLACEINTO z SELECT I; -- replace must be called repeatedly to avoid repeated primary key insert errors.

SET I = I + 1;

ENDWHILE;

COMMIT;

END $

DELIMITER;

 

3.2 undo

Undo records are the opposite of redo records. insert is changed to delete, update is changed to update, and redo is placed in redo file. While undo is placed on an internal special segment to store and share the tablespace (in ibdata1 or ibdata2 ). Reference: http://blog.csdn.net/mchdba/article/details/8664943

Because it is implemented by executing the opposite dml statement. In addition, the newly added pages due to insert and upate will not be recycled. The undo page will be recycled through the masterthread thread.

 

 

4. transaction control statements

By default, transactions are automatically committed in the mysql command line. After the SQL statement is submitted, the commit operation is executed immediately. Therefore, to start a transaction, you must use begin, start transaction, or execute the set autocommit = 0; transaction control statement that can be used.

Starttransction | begin: displays opening a transaction, refer to http://blog.csdn.net/mchdba/article/details/8690935

SQL statements implicitly submitted: (1) ddl statements, alter database ..., Alter event, alter procedure, alter table, alterview, create table, drop table, rename table, truncate table, etc.; (2) Modify mysql schema statements, createuser, drop user, grant, rename user, revoke, set password. (3) Manage statements, such as analyze table, cache index, check table, load index into cache, optimizetable, and repair table.

[The truncate table statement is ddl and cannot be rollback. This is different from mssqlserver.]


5. Transaction operation Statistics

Innodb supports transactions. Therefore, for InnoDB Storage engine applications, when considering the number of requests Per Second (Question Per Second, QPS), we should pay more attention to TPS, that is, the Transaction processing capacity Per Second (TPS ).

The TPS calculation method (com_commit + com_rollback)/tim. This calculation method must have one precondition: all transactions must be committed. If there is an implicit commit and rollback (autocommit = 1 by default, or the value is on)

mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON   |+---------------+-------+1 row in set (0.00 sec) mysql>mysql> show global status like'com_commit';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_commit    | 0    |+---------------+-------+1 row in set (0.00 sec) mysql> use test;Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| t              || t1             || t3             || z              |+----------------+4 rows in set (0.00 sec) mysql> insert into z select 1;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0  Warnings: 0 mysql> select * from z;+---+| a |+---+| 1 |+---+1 row in set (0.00 sec) mysql> show global status like'com_commit';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_commit    | 0    |+---------------+-------+1 row in set (0.00 sec) mysql>

 

In addition, mysql also has two parameters: handler_commit and handler_rollback. In mysql5.1, these two parameters can well count innodb display and implicit transaction commit operations, the statistical values of these two parameters in innodb plugin are somewhat odd, and the number of transactions cannot be well measured.

mysql> show global status like'Handler_commit';+----------------+-------+| Variable_name  | Value |+----------------+-------+| Handler_commit | 17    |+----------------+-------+1 row in set (0.00 sec) mysql> show global status like'Handler_rollback';+------------------+-------+| Variable_name    | Value |+------------------+-------+| Handler_rollback | 0     |+------------------+-------+1 row in set (0.00 sec) mysql>


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.