Shulou(Shulou.com)05/31 Report--

This article mainly explains "mysql transaction knowledge point analysis". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the small editor's thinking slowly and deeply to study and learn "mysql transaction knowledge point analysis" together!

1. Business 1.1. characteristics of transaction

The InnoDB storage engine supports transactions. By default, it is self-commit mode. To start a transaction, it must start with the begin command and end with commit or rollback.

transaction property

Atomicity: Do all or nothing.

Consistency: Business rule constraints are met before and after operations.

Isolation: Multiple concurrent transactions do not affect each other.

Durabili: End of transaction, modifications to data are persistent.

1.2. transaction statement

Start transaction(read write)| Read only) command starts and ends with commit or rollback.

Implicit commit: DDL operation or re-enter the begin and start transaction commands.

Implicit rollback: session exit, connection timeout, shutdown, etc.

Turn off autocommit (set autocommit=0)

Enable autocommit (set autocommit=1)

Turn off automatic commit benefits: do not commit a transaction at once, multiple transactions together pride, improve processing power.

Disadvantages of closing self-commit: If a transaction is not committed for a long time, it will cause row lock waiting and affect the TPS value of the database.

It is not recommended to turn off self-commit mode.

1.3. Difference between truncate and delete

truncate is DDL, the transaction can not be rolled back, will clear the table from the increasing attribute, back to the original starting value.

delete Yes DML

Common denominator: clearing tables,

1.4. Isolation level of transactions

SQL standard 4-hour isolation level

n read uncommitted,RU, a transaction can read uncommitted data changes from other transactions, called dirty read, not recommended for production environments.

n Read committed,RC, a transaction can read data changes committed by other transactions, called non-repeatable read, is Oracle default transaction isolation level.

n Repeatable read,RR, In a transaction, until the end of the transaction, the data seen at the beginning of the transaction can be repeatedly read, and no change has occurred, avoiding dirty read, non-repeatable read, and phantom read phenomena. mysql default transaction isolation level.

n serializable, adding a table-level shared lock to each read data line, and adding a table-level exclusive lock to each write data. InnoDB causes concurrency degradation, large timeouts, and lock contention, and is not recommended for production environments.

View current library isolation level

[mysql]>show variables like '%tx_isolation%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

Modify the transaction isolation level for the global or current session

set global|session transaction isolation level

1.5. Dirty, non-repeatable, phantom, repeatable 1.5.1. dirty reads

RU, a transaction reads uncommitted data from another transaction.

1.5.2. Irrepeatable Reading and Illusion Reading

Non-repeatable read: A transaction reads a modification record of other transactions against old data, common (update,delte).

Phantom read: A transaction reads new data from another transaction, common (insert), allowed to appear in the isolation level of committed transactions.

1.5.3. repeatable read

Repeatable reads are mysql default transaction isolation levels, eliminating dirty reads, non-repeatable reads, phantom reads and other phenomena, and ensuring transaction consistency.

If you want to read other new data in this transaction, you can do the following:

Select * from t for update;

This transaction is submitted once: commit;

Thank you for reading, the above is the content of "mysql transaction knowledge point analysis". After studying this article, I believe everyone has a deeper understanding of mysql transaction knowledge point analysis. The specific use situation still needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.