The third statement inserts a record of the transfer into the journal table. The second statement adds $500 to checking account 3208. The first statement subtracts $500 from savings account 3209. However, if a problem such as insufficient funds, invalid account number, or a hardware failure prevents one or two of the statements in the transaction from completing, then the database must roll back the entire transaction so that the balance of all accounts is correct.įigure 10-1 illustrates a banking transaction. If all three SQL statements maintain the accounts in proper balance, then the effects of the transaction can be applied to the database. Oracle Database must allow for two situations. Record the transaction in the transaction journal When a customer transfers money from a savings account to a checking account, the transaction must consist of three separate operations: To illustrate the concept of a transaction, consider a banking database. Sample Transaction: Account Debit and Credit The use of transactions is one of the most important ways that a database management system differs from a file system.
After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost. Thus, it appears to users as if transactions are executing serially.Ĭhanges made by committed transactions are permanent. For example, one user updating the hr.employees table does not see the uncommitted changes to employees made concurrently by another user. The effect of a transaction is not visible to other transactions until the transaction is committed. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data. The transaction takes the database from one consistent state to another consistent state. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows. ACID is an acronym for the following:Īll tasks of a transaction are performed or none of them are. Oracle Database assigns every transaction a unique identifier called a transaction ID.Īll Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. A transaction is a logical, atomic unit of work that contains one or more SQL statements.
0 kommentar(er)
