Database Transactions and Locks (ii)

Data | database
Working diagram of a transaction

Transactions ensure the consistency and recoverability of data. The transaction works as shown in Figure 1.



Figure 1 Working schematic diagram of a transaction

After the transaction starts, all the operations of the Office are written to the transaction log in succession. There are generally two types of operations written to the log: one for data manipulation and one for task operations. Operations on data, such as insertions, deletions, and modifications, are typical transaction operations, which are objects of large amounts of data. Some operations are for tasks, such as creating indexes, which record a flag in the transaction log to indicate that the action was performed. When this transaction is canceled, the system automatically performs the reverse operation of this operation to ensure the consistency of the system. The system automatically generates a checkpoint mechanism, and this checkpoint occurs periodically. The checkpoint cycle is the time interval that the system automatically calculates from the system based on user-defined time intervals and the frequency of system activity. The checkpoint periodically checks the transaction log, and if the transaction is complete in the transaction log, the checkpoint commits the transaction in the transaction log to the database and makes a checkpoint submission mark in the transaction log. If the transaction is not completed in the transaction log, the checkpoint does not commit the transaction in the transaction log to the database, and a checkpoint is not committed in the transaction log. Transaction recovery and the integrity and recoverability of the checkpoint protection system, you can use the example instructions shown in Figure 2.



Figure 2 Transaction Recovery and checkpoint example

In this example diagram, there are five transactions: Transaction 1, Transaction 2, transaction 3, transaction 4, and transaction 5. The box represents the start and completion commit of a transaction. The horizontal direction represents the time. A checkpoint indicates that a checkpoint mechanism occurred at a point in time, and the system failure indicates a system failure that occurred at a point in time due to power outages, system software failure, and so on. The completion of transaction 1 occurs between checkpoints, so transaction 1 is committed to the database. The completion of transaction 2 and transaction 4 occurs before the system fails, so the two transactions can be submitted to the database by the system forward scrolling. Transaction 3 and transaction 5 were canceled because the system failed, so the two transactions were cancelled.