Database Review 6--transactions

Database review CH13 Transaction 13.1 Concepts

A transaction is a series of database operations performed as a single logical unit of work that may modify multiple tuples in multiple tables

The structure of the normal execution of a transaction is:

12...SQL execution Ncommit;

The BEGIN flag starts a transaction, and multiple SQL statements are transactional logical units of work, commit(commit) is the end of the current transaction and commit the data changes within the transaction to take effect

Database Consistency behaves more specifically in terms of transactional execution, where the database can be in an inconsistent state, but once the commit database must return to a consistent state (consistency refers to a generalized consistency, that is, real-world data consistency, If A and B transfer their total account balance unchanged etc.)

Before you understand the specific role of a transaction and the transaction usage scenario, first look at the acid nature of the transaction:

• Atomicity: Atomicity, a transaction either all operations are executed, or none of them are executed
• Consistency: Consistency, view a transaction independently, it must ensure database consistency
• Isolation: Independence, multiple transactions allow concurrent execution, but you must ensure that transactions do not interfere with each other, and that one transaction execution process should be transparent to other transactions
• Durability: Persistent, when a transaction completes successfully, its changes to the database must persist

Bank Transfer is a favorite example of a transaction, and account a transfers m to account B, which is expressed as a transaction (abstract, non-SQL, which divides the update into multiple steps):

read(A)A = A - Mwrite(A)read(B)B = B + Mwrite(B)

This transfer process is the business logic that the bank database often needs to complete, and we can encapsulate such frequently invoked transactions as stored procedures for frequent calls (see chapter on stored procedures)

The ACID properties are specifically reflected in this stored procedure:

• A: Either the 6 steps of the transfer process are completed or all are not completed, if A's account is less than M, the third step write failure (integrity constraints), must ensure that the previous operation has no impact on the database
• The sum of c:a and B must be guaranteed to be constant.
• I: Other concurrent transfer transactions or other transactions related to a and B have no effect on the current transfer
• D: Once the two account holders of A and B have received information about the successful transfer, the transaction must be permanently in force, even if the system fails (the division of responsibilities is clear, the transfer is correct, even if the failure is not recoverable, it should be the responsibility of the banking system)

13.2 Transaction Management

Before you understand transaction management, you must understand that a transaction may be in a state during execution:

• Active: The initial state of the transaction, which is persisted at execution time
• Partially committed: State after the last operation was executed (not yet commit)
• Failed: Error found, stop execution
• Aborted: The transaction is rolled back (rollback) to the state before the transaction is executed, and after aborted there are two choices: re-execution (if there is no logical error inside the transaction), kill the transaction
• Committed: The state after the transaction has completed successful execution

Transaction state transition relationships such as:

From the ACID properties and the transaction state relationships above, the database transaction management mainly handles two things:

• Error/failure: recovery processing when transaction execution fails (logical error, System crash, power outage, and so on)
• Concurrent execution: Multiple transactions can be executed concurrently (the meaning of concurrency is to increase CPU and disk utilization, and to reduce the average response time), and to prevent the compromise of database consistency between concurrent transactions

13.3 SQL transactions

PPT says, "in SQL, a transaction begins implicitly", but my online search and my own test (OSX on SQLite) need to explicitly call begin; or begin transaction [name]; represent the beginning of a transaction

SQL transaction supports two statements: commit [transaction]; and rollback; , meaning is clear, end or end transaction [name]; can also end the transaction and default commit

(1) Test declaration transactions and rollback

Build table → start an anonymous transaction → insert a tuple →select view (inserted) →rollback test

integerkeytextinto Stu values(1‘jcguo‘);selectfrom Stu;1selectfrom Stu;

The SQLite command line has no output, indicating that Stu is an empty table that successfully rolls back the insert

(2) Whether the transaction ends after the test rollback

After rollback the SELECT statement → insert two tuples →select view (inserted) →commit command to test whether the transaction has just ended

into Stu values(1‘jcguo‘);into Stu values(2‘wp‘);selectfrom Stu;1|jcguo2|wpsqlite> commit;Erroris active

The DBMS returned an error stating that the transaction had ended and that the commit command could not be executed when no transaction was executed (that is, the SQL transaction is not performed implicitly, as in PPT)

(3) Test commit

begininto Stu values(3‘wpo‘selectfrom Stu;1|jcguo2|wp3|wpo

Post-insert Commit, transaction executed successfully

(4) Test transaction logic Error

into Stu values(3‘wpo‘);Error: UNIQUE constraint failed: Stu.idsqlite> rollback;sqlite> rollback;Erroris active

Insert a tuple that throws an integrity constraint failure, and the transaction does not implicitly enter the failed state and automatically aborted, I still do it manually rollback

Note:

1. Originally intended for business, recovery, and concurrency in a chapter, found that the latter two more content or better apart
2. The above test is not perfect, SQLite is not the mainstream DBMS, subsequent recovery and concurrency will have more testing
3. Search finds some DBMS support set implicit_transactions on/off; statements to turn implicit transactions on or off

Database Review 6--transactions