A series of operations on a database treated as a single unit. COMMIT makes the results permanent, and ROLLBACK returns the data to its state before the transaction began. The DBMS manages transactions with locks and logs so that they satisfy atomicity, consistency, isolation and durability (the ACID properties).
Japanese name: トランザクション
Transaction appears in 13 practice questions across 1 topic, including Database Basics.
Field: Database Basics
In a transfer between accounts, the result is correct only when both the debit from account A and the credit to account B have been made. A transaction groups such a series of operations into one unit: executing COMMIT at the end makes the results permanent, and executing ROLLBACK, for example when an error occurs partway through, cancels every update in the transaction. Uncommitted updates are also cancelled if a failure occurs, so no half-finished state with only some updates applied remains.
When many users access a database at the same time, several transactions read and write the same data. Without any control, problems occur: one update can overwrite and lose another (a lost update), or a transaction can read uncommitted data (a dirty read). The DBMS prevents interference between transactions with concurrency control using shared and exclusive locks, or with multiversion concurrency control (MVCC), which keeps earlier versions of data for reads. How much interference to prevent is chosen with the isolation level.
The DBMS records the before and after values of updates in a log and writes the log to storage before the database itself. After a failure, it uses the log to redo the updates of committed transactions (rollforward) and undo the updates of uncommitted ones (rollback), restoring a correct state. Taking checkpoints regularly shortens the range of log that must be traced during recovery.
Answer: B. Both UPDATEs are cancelled, and the table returns to its state before the transaction started
A transaction is a mechanism that treats a series of operations on a database as a single unit. Updates made within a transaction become permanent when COMMIT is executed, and are all cancelled when ROLLBACK is executed, returning the data to its state before the transaction started. In this example, both the subtraction from account A and the addition to account B are cancelled, and the two balances are unchanged. If an error occurs partway through a transfer, rolling back ensures that no half-finished state with only one account updated remains. To cancel only part of the work, you can mark a point with SAVEPOINT and return to it with ROLLBACK TO SAVEPOINT.
Answer: C. Atomicity
The ACID properties are named after the initials of four properties a transaction should satisfy. Atomicity means the operations in a transaction are either all executed or none are; if a failure occurs, the updates made so far are cancelled. Consistency means the data keeps satisfying integrity constraints (balances never negative, foreign keys always referring to existing rows, and so on) before and after the transaction. Isolation means that transactions running concurrently do not interfere with each other and produce the same result as if they had been executed one at a time. Durability means that the results of a committed transaction are not lost even if a failure occurs afterwards. The all-or-nothing property in the question is atomicity.
Either the application executes ROLLBACK or the DBMS performs failure recovery, and every update in that transaction is cancelled. Only some of the updates are never left behind.
It holds locks for a long time, so other transactions wait, less work can be processed concurrently, and deadlocks become more likely. As a rule, a transaction should be kept as short as is needed to maintain consistency.
A transaction is a mechanism that treats a series of operations on a database as a single unit.
In an account table, a transaction is started, and an UPDAT…The ACID properties are named after the initials of four properties a transaction should satisfy.
Among the ACID properties a transaction should have, which …When a search with the same condition is repeated within one transaction and the set of result rows changes because another transaction added (or deleted) matching rows and committed in between, the phenomenon is called a phantom read (or phantom).
Transaction T1 ran SELECT COUNT(*) FROM orders WHERE amount…Waiting alone never resolves it, so many DBMSs detect a deadlock, for example when a cycle appears in the wait-for graph that shows which transaction is waiting for which, and roll back one of them (the victim) to release its locks.
Transaction T1 took an exclusive lock on row A and then req…COMMIT, which makes a transaction permanent, and ROLLBACK, which cancels it, are sometimes counted as DCL and sometimes treated separately as transaction control statements.
SQL statements are divided into the data definition languag…As an Amazon Associate I earn from qualifying purchases.
SQL ACID properties Isolation level Deadlock Normalization Two-phase locking (2PL) Database index Database view E-R diagram Foreign key