YouTipTRANSACTION (transaction) is a logical unit of execution in a database management system, consisting of a finite sequence of database operations.
\n\nA database transaction typically includes a sequence of read/write operations on the database. It serves two main purposes:
\n\n- \n
- Provides a method for recovering from failures to a normal state for the sequence of database operations, while also ensuring the database remains consistent even in abnormal states. \n
- When multiple applications access the database concurrently, it provides an isolation method between these applications to prevent their operations from interfering with each other. \n
When a transaction is submitted to the Database Management System (DBMS), the DBMS must ensure that all operations within that transaction are successfully completed and their results are permanently saved in the database. If any operation within the transaction fails to complete, all operations in the transaction must be rolled back to the state before the transaction was executed. At the same time, the transaction should not affect the execution of the database or other transactions; all transactions should appear to run independently.
\n\nTransaction Properties
\n\nTransactions have the following four standard properties, commonly abbreviated as ACID:
\n\n- \n
- Atomicity (atomicity): The transaction is executed as a whole; all database operations contained within it are either all executed or none are executed. \n
- Consistency (consistency): The transaction should ensure the database transitions from one consistent state to another. A consistent state means the data in the database satisfies integrity constraints. \n
- Isolation (isolation): When multiple transactions execute concurrently, the execution of one transaction should not affect the execution of other transactions. \n
- Durability (durability): Modifications made by a committed transaction should be permanently saved in the database. \n
Example
\n\nSomeone wants to buy 100 yuan worth of goods from a store using electronic currency. This involves at least two operations:
\n\n- \n
- Reduce the person's account by 100 yuan. \n
- Increase the store's account by 100 yuan. \n
A database management system that supports transactions must ensure that both of these operations (the entire "transaction") are completed, or both are canceled together. Otherwise, 100 yuan could disappear or appear out of nowhere.
\n\nTransaction Control
\n\nUse the following commands to control transactions:
\n\n- \n
- BEGIN TRANSACTION: Starts a transaction. \n
- COMMIT: Confirms the transaction, or you can use the
END TRANSACTIONcommand. \n - ROLLBACK: Rolls back the transaction. \n
Transaction control commands are only used with INSERT, UPDATE, and DELETE. They cannot be used when creating or deleting tables, as these operations are automatically committed in the database.
BEGIN TRANSACTION Command
\n\nA transaction can be started using the BEGIN TRANSACTION command or simply the BEGIN command. Such a transaction typically continues to execute until the next COMMIT or ROLLBACK command is encountered. However, if the database is closed or an error occurs, the transaction processing will also roll back. Here is the simple syntax to start a transaction:
BEGIN;\n-- or\nBEGIN TRANSACTION;\nCOMMIT Command
\n\nThe COMMIT command is a transaction command used to save the changes made by the transaction to the database, i.e., to confirm the transaction.
The syntax for the COMMIT command is as follows:
COMMIT;\n-- or\nEND TRANSACTION;\nROLLBACK Command
\n\nThe ROLLBACK command is a transaction command used to undo changes that have not yet been saved to the database, i.e., to roll back the transaction.
The syntax for the ROLLBACK command is as follows:
ROLLBACK;\nExample
\n\nCreate the COMPANY table (Download COMPANY SQL file), with the following data content:
\n\ntutorialdb# select * from COMPANY;\n id | name | age | address | salary\n----+-------+-----+------------+--------\n 1 | Paul | 32 | California | 20000\n 2 | Allen | 25 | Texas | 15000\n 3 | Teddy | 23 | Norway | 20000\n 4 | Mark | 25 | Rich-Mond | 65000\n 5 | David | 27 | Texas | 85000\n 6 | Kim | 22 | South-Hall | 45000\n 7 | James | 24 | Houston | 10000\n(7 rows)\nNow, let's start a transaction and delete the records where age = 25 from the table. Finally, we use the ROLLBACK command to undo all changes.
tutorialdb=# BEGIN;\nDELETE FROM COMPANY WHERE AGE = 25;\nROLLBACK;\nCheck the COMPANY table; it still has the following records:
\n\n id | name | age | address | salary\n----+-------+-----+------------+--------\n 1 | Paul | 32 | California | 20000\n 2 | Allen | 25 | Texas | 15000\n 3 | Teddy | 23 | Norway | 20000\n 4 | Mark | 25 | Rich-Mond | 65000\n 5 | David | 27 | Texas | 85000\n 6 | Kim | 22 | South-Hall | 45000\n 7 | James | 24 | Houston | 10000\nNow, let's start another transaction, delete the records where age = 25 from the table, and finally we use the COMMIT command to submit all changes.
tutorialdb=# BEGIN;\nDELETE FROM COMPANY WHERE AGE = 25;\nCOMMIT;\nCheck the COMPANY table; the records have been deleted:
\n\n id | name | age | address | salary\n----+-------+-----+------------+--------\n 1 | Paul | 32 | California | 20000\n 3 | Teddy | 23 | Norway | 20000\n 5 | David | 27 | Texas | 85000\n 6 | Kim | 22 | South-Hall | 45000\n 7 | James | 24 | Houston | 10000\n(5 rows)\n