MySQL Advanced Ten - Application of Transactions

1. What is a transaction

Why should we use transaction technology? Nowadays, many software are multi-user, multi-course, and multi-threaded. The same table may be used by many people at the same time. In order to maintain the consistency of the data, the concept of transactions is proposed.

1. Check whether the database supports transactions (InnoDB supports it)?

show engines;

2. Check the current default storage engine of mysql?

show variables like '%storage_engine%';

3. Check the storage engine of a certain table?

show create table test;

4. Modification of the storage structure of the table?

Create an InnoDB table: Create table ... type=InnoDB;Alter table table_name type=InnoDB;

2. Example:

1. Create a database bank

create table account(  
aid int not null,  
accname varchar(20) not null,  
accmoney decimal(10,2) not null,  
primary key(aid))engine = innodb default charset = gbk;

2. Insert two pieces of data into the account table

insert into account values(1,'A',4000);  
insert into account values(2,'B',2000);

3. Start the transaction of the table

start transaction;

4. Set the data to active submission

commit;  
set autocommit = 0;

5. Execute the following statement

<pre name="code" class="sql">update account set accmoney = accmoney - 1000 where aid = 1;  
update account set accmoney = accmoney + 1000 where aid = 2;

6. Open a new MySQL command window to view the account table

select * from account;

. You will find that the data has not changed, because the above modified instructions have not been modified by the database. Command;

7. Query in the original window and you will find that the data has changed

select * from account;

8. Submit the physical commit

commit;

9. In another client If you check the data, you will find that the data has changed

select * from account;

3. Transaction rollback and restore point

1. Transaction rollback

Return to the data state before the transaction occurred. through rollback.

Supplement: commit and chain; means that a new transaction is reopened after the transaction is submitted.

rollback and release; means disconnecting from the customer after rollback.

2. Restore point (instance)

set autocommit = 0;  
insert into account values(3,'C',3000);  
savepoint a1;  
insert into account values(4,'D',3000);  
savepoint a2;  
insert into account values(5,'E',3000);  
savepoint a3;

View database information

就会看到你插入的数据

如果你想回滚到某一状态只需rollback调用一下就行；

如：回到savepoint a1的状态

rollback to savepoint a1;

Then execute the query statement

You will see that there is an entry in the table Newly added data.

4. Transaction Summary

Transactions should have four attributes:

Atomicity, consistency, isolation, and durability. These four properties are often called ACID properties.

Atomicity: A transaction is an indivisible unit of work. All operations included in the transaction are either done or none.

Consistency: A transaction must change the database from one consistency state to another consistency state. Consistency and atomicity are closely related.

Isolation: The execution of a transaction cannot be interfered by other transactions, that is, the internal operations and data used by a transaction are isolated from other concurrent transactions, and concurrently executed transactions cannot interfere with each other.

Durability: Durability also becomes permanent. Once a transaction is committed, its changes to the data in the database should be permanent. Subsequent operations or failures should not have any impact on it.

The above is the content of MySQL Advanced Ten - Transaction Application. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!