Mysql’s transaction

The transaction in MySQL is the smallest indivisible unit of work, and the transaction can ensure the integrity of a business

There are multiple SQL statements in a business, and these SQL statements must be successful at the same time or At the same time, it fails. At this time, a transaction is required to ensure this requirement.

For example: a transfer 100 from your bank card to b

update user set money=money-100 where name=’a’;

update user set moeny=money+100 where name=’b’;

These two sql statements must succeed or fail at the same time, which requires a transaction

MySQL is automatically opened for transaction And automatically submit data, so that every SQL statement executed will submit the data to the database, so that a transaction is completed

The record submitted to the database cannot be undone, so if the following SQL statement is executed No, the previous sql cannot be withdrawn.

We can turn off the automatic opening of transactions and automatic submission of data, so that we can set up multiple SQL statements in a transaction, when they are all executed, submit data to the database, so that we can solve the problem of multiple SQL statements Successful operations of the two statements at the same time

Revocation of uncommitted SQL operations as rollback

Turn off automatic open transaction and automatic data submission set autocommit =0;

Open transaction :Begin/start transaction;

Manually submit data :commit


Share a picture

Set the transaction manually and turn off the automatic data submission function. The multiple sql operations we performed on cmd will not change Submit the data to the database to manually start the transaction begin;

We view the database data in another cmd and are not affected by it

share picture

We manually submit the data commit

share picture

At this time, the operation records in cmd are transferred to the database. Before submitting, a rollback can cancel all sql statements in this transaction

After submitting, the database will modify the data in the database.

share picture

Four characteristics of transactions

1: Atomicity Transaction is the smallest unit and cannot be split

2: Consistency SQL statements in a transaction must succeed or fail at the same time

< h4>3: Isolation Different transactions will not affect each other

4: Persistence After the transaction is completed, the data cannot be withdrawn.

Leave a Comment

Your email address will not be published.