Summary of transaction knowledge points

Time:2021-9-9
  1. The concept of transaction: a set of database operations, either all successful or all failed;
  2. Transaction characteristics: atomicity, consistency, isolation and persistence;
  3. Possible problems when multiple transactions of the same record are executed at the same time: dirty reading, non repeatability, phantom reading; (in order to solve these problems, the concept of isolation level appears)
  4. Isolation level: read uncommitted, read committed, repeatable read, serialization; (execution efficiency decreases and safety increases in turn)
    • Read uncommitted: if a transaction has not been committed, its changes can be seen by another transaction;
    • Read commit: after a thing is submitted, its changes can be seen by another transaction;
    • Repeatable reading: the data seen during the execution of a transaction is always consistent with the data seen when it is started. Of course, at this level, other transactions will not be visible until the changes are committed;
    • Serialization: for the same record, “read” will add read lock, and “write” will add write lock. When there is a read-write lock conflict, the post accessed transaction cannot continue until the previous transaction is completed.
  5. Implementation of isolation level: by creating a view at the bottom of the database, the whole process of access is subject to the logical result of the view; In addition, when each record is updated, a rollback operation will be recorded at the same time; Therefore, there may be multiple versions of the same record in the system at the same time, that is, multi version concurrency control (mvcc).
    • Read uncommitted: directly returns the latest value on the record, without the concept of view;
    • Read commit: Yes on eachsqlCreate a view when the statement is executed;
    • Repeatable reading: creates a view when a transaction is started;
    • Serialization: it uses the form of locking to avoid parallel access;
  6. Configuration parameters of transaction isolation level:transaction-isolation;
  7. When is the rollback log cleared? When there is no reda view earlier than this rollback log in the system;
  8. Transaction startup mode: display startup mode;begin transaction ; commit ; rollback;
  9. set autocommit = 0 ;orset autocommit = 1

    It is recommended to always useset autocommit = 1, and then displays the start transaction of the

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