Lecture 5

transaction
a transaction is any action that reads from and/or writes to a database. a transaction may consist of a simple select statement to generate a list of table contents it may consist of series of insert statements to add rows to one or more tables.
transaction properties
each individual transaction must display atomicity, consistency, isolation, and durability. these properties are sometimes referred to as the acid test. in addition, when executing multiple transactions, the dbms must schedule the concurrent execution of the transaction’s operations. the schedule of such transaction’s operations must exhibit the property of serializability. let’s look brie?y at each of the properties.
• atomicity requires that all operations (sql requests) of a transaction be completed if not, the transaction is aborted. if a transaction t1 has four sql requests, all four requests must be successfully completed otherwise, the entire transaction is aborted. in other words, a transaction is treated as a single, indivisible, logical unit of work.
• consistency indicates the permanence of the database’s consistent state. a transaction takes a database from one consistent state to another consistent state. when a transaction is completed, the database must be in a consistent state if any of the transaction parts violates an integrity constraint, the entire transaction is aborted.
• isolation means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed. in other words, if a transaction t1 is being executed and is using the data item x, that data item cannot be accessed by any other transaction (t2 tn) until t1 ends. this property is particularly useful in multiuser database environments because several users can access and updating the database at the same time.
• durability ensures that once transaction changes are done (committed), they cannot be undone or lost, even in the event of a system failure.
• serializability ensures that the schedule for the concurrent execution of the transactions yields consistent results. this property is important in multiuser and distributed databases, where multiple transactions are likely to be executed concurrently. naturally, if only a single transaction is executed, serializability is not an issue.
a single-user database system automatically ensures serializability isolation of the database because only one transaction is executed at a time. the atomicity, consistency, and durability of transactions must be guaranteed by the single-user dbmss. (even a single-user dbms must manage recovery from errors created by operating-system-included interruptions, power interruptions, and improper application execution.
multiuser databases are typically subject to multiple concurrent transactions. therefore, the multiuser dbms must implement controls to ensure serializability and isolation of transactions - in addition to atomicity and durability - to guard the database’s consistency and integrity. for example, if several concurrent transactions are executed over the same data set and the second transaction updatings the database before the first transaction is finished, the isolation property is violated and the database is no longer consistent. the dbms must manage the transactions by using concurrency control techniques to avoid such undesirable situations.
transaction management with sql
transaction support is provided by two sql statements: commit and rollback. when a transaction sequence is initiated by a user or an application program, the sequence must continue through all succeeding sql statements until one of the following four events occurs:
• a commit statement is reached, in which case all changes are permanently recorded within the database. the commit statement automatically ends the sql transaction.
• a rollback statement is reached, in which case all changes are aborted and the database is rolled back to its previous consistent state.
• the end of a program is successfully reached, in which case all changes are permanently recorded within the database. this action is equivalent to commit.
• the program is abnormally terminated, in which case the changes made in the database are aborted and the database is rolled back to its previous consistent state. this action is equivalent to rollback.
the use of commit is illustrated in the following simplified sales example, which updatings a product’s quantity on hand (prod_qoh) and the customer’s balance when the customer buys two units of product 1558-qw1 priced at $43.99 per unit (for a total of $87.98) and charges the purchase to the customer s account:
updating product
set prod_qoh= prod_qoh— 2
where prod_code = 1558-qw1
updating customer
set cust_bai_ance = cust_balance + 87.98
where cust_number = 10011
commit
actually, the commit statement used in that example is not necessary if the updating statement is the application’s last action and the application terminates normally. however, good programming practice dictates that you include the commit statement at the end of a transaction declaration.

the transaction log
a dbms uses a transaction log to keep track of all transactions that updating the database. the information stored in this log is used by the dbms for a recovery requirement triggered by a rollback statement, a program abnormal termination, or a system failure such as a network discrepancy or a disk crash.
while the dbms executes transactions that modify the database, it also automatically updatings the transaction log. the transaction log stores:
• a record for the beginning of the transaction.
• for each transaction component (sql statement):
- the type of operation being performed (updating, deleting, insert).
- the names of the objects affected by the transaction (the name of the table).
- the “before’ and “after” values for the fields being updatingd.
- pointers to the previous and next transaction log entries for the same transaction.
• the ending (commit) of the transaction.
trl_ id trx_ num prev ptr next ptr operation table row id attribute before value after value
341 101 null 352 start ****start transaction
352 101 341 363 updating product 1558-qw1 prod_qoh 25 23
363 101 352 365 updating customer 10011 cust_balance 525.75 615.73
365 101 363 null commit **** end of transaction



trl_id = transaction log record id
trx_num = transaction number
(note: the transaction number is automatically assigned by the dbms.

ptr =pointer to a transaction log record id