Summer self-study day 14 | database (VII) – affairs

Time:2021-10-21

affair

  • After multiple operations are managed by transactions, they either fail or succeed at the same time; Avoid safety problems.
  • Operation:
    • Start: start transaction;
    • End: commit;
    • Rollback: rollback;
  • DML in MySQL automatically commits transactions by default
    • Set manual submissionSET @@autocommit = 0;

Four characteristics of transaction***

  • Atomicity: the smallest indivisible unit of operation that either succeeds or fails at the same time.
  • Persistence: after the transaction is committed or rolled back, the database will persist and save the data.
  • *Isolation: multiple transactions are independent of each other Isolation level of exported data
  • Consistency: the total amount of data remains unchanged before and after the transaction operation.

Transaction isolation level

  • Multiple transactions operate on the same set of data, which will cause some problems. Different isolation levels need to be set to solve the problem.
  • Existing problems:
    • Dirty read: one transaction reads uncommitted data from another transaction.
    • Non repeatable read (virtual read): in the same transaction, the data read twice is different.
    • Phantom reading: if one transaction operates all records in the data table and another transaction adds a piece of data, the first transaction cannot query its own modifications.
  • Several levels
    • read uncommitted
    • Read committed: resolve dirty read, Oracle default level
    • Repeatable read: solves dirty and virtual reads. MySQL is the default level
    • Serializable: solving all problems is equivalent to locking the operation. Only one transaction can execute another transaction after it is completed.
  • levelandSecurityFrom low to high,efficiencyFrom high to low
  • View transaction level:SELECT @@tx_isolation;
  • Modify transaction isolation level:Set global transaction isolation level string;

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

SQL statement of three-level linkage of provinces, cities and counties

The first is the table creation statement Copy codeThe code is as follows: CREATE TABLE `t_address_province` ( `id` INT AUTO_ Increment primary key comment ‘primary key’,`Code ` char (6) not null comment ‘province code’,`Name ` varchar (40) not null comment ‘province name’)Engine = InnoDB default charset = utf8 comment = ‘province information table’; CREATE TABLE […]