5138 COMP Relational Database Management Systems
Semester 2, 2007 Lecture 8A Transaction Concept
L11
Transaction Management
Definition
A transaction is a collection of one or more operations on one or more databases, which reflects a single real-world transition In the real world, this happened (completely, all at once) or it didnt happen at all Commerce examples Transfer money between accounts Purchase a group of products, and mark order as filled Student record system Register for a class (either waitlist or allocated) Many DB-based computer systems are mainly used for processing transactions
2
L11
Transaction Management
Coding a transaction
L11
Transaction Management
COMMIT
Typically a computer-based system doing OLTP has a collection of parameterized application programs Each program is written in a high-level language, which calls DBMS to perform individual SQL statements Either through embedded SQL converted by preprocessor Or through Call Level Interface where application constructs appropriate string and passes it to DBMS Can use program variables in queries A single SQL statement cant do it all! Need to update several tables Need control flow logic
As app program is executing, it is in a transaction Program can execute COMMIT SQL command to finish the transaction successfully The next SQL statement will automatically start a new transaction Even working interactively, all activity on dbms is in a txn Each interactive SQL statement is separate txn
3 4
L11
Transaction Management
ROLLBACK
L11
Transaction Management
Transaction Definition in SQL
If the app gets to a place where it cant complete the transaction successfully, it can execute ROLLBACK This causes the system to abort the transaction The database returns to the state without any of the previous changes made by activity of the transaction Rollback can be due to: Program request Client process death System-initiated (eg to deal with deadlock or crash)
5
Data manipulation language must include a construct for specifying the set of actions that comprise a transaction. In many DBMS such as Oracle, a transaction begins implicitly
Some other DBMS provide a BEGIN TRANSACTION command
A transaction in SQL ends by:
Commit commits current transaction and begins a new one. Rollback causes current transaction to abort.
6
1
L11
Transaction Management
Transaction Example
L11
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19.
Transaction Management
Transaction Example
Transaction in Embeded SQL
Pseudocode for a product order transaction:
START TRANSACTION display greeting get order request SELECT product record If product is available Then UPDATE quantityOnOrder of product record INSERT order record send message to shipping departement End if On Error: ROLLBACK COMMIT
EXEC SQL BEGIN DECLARE SECTION int flight; START TRANSACTION char date[10] char seat [3] int occ; EXEC SQL END DECLARE SECTION Void chooseSeat() { /*C code to prompt the user to enter flight, date, and seat and store these in the three with variables those name */ EXEC SQL Select occupied into :occ From Flights Where fltNum=:flight and fltDate=:date and fltSeat=:seat; If (!occ) { EXEC SQL Update Flights Set occupied = true Where fltNum=:flight and fltDate=:date and fltSeat=:seat; /*C and SQL code to record the seat assignment and inform the user of the assignment */ } Else /* C code to notify user of unavailability and ask for another seat selection */ }
7
8
L11
Transaction Management
Atomicity
L11
Transaction Management
Integrity
Two possible outcomes for a transaction It commits: all the changes are made It aborts: no changes are made That is, transactions activities are all or nothing Also, the decision is irrevocable; so once committed, the transaction cant revert to aborted Changes are durable
A real world state is reflected by collections of values in the tables of the DBMS But not every collection of values in a table makes sense in the real world The state of the tables is restricted by integrity constraints Perhaps, but not always, declared explicitly in the database schema Eg foreign key constraint, primary key constraint
9
10
L11
Transaction Management
Consistency
L11
Transaction Management
System threats to data integrity
Each transaction can be written on the assumption that all integrity constraints hold in the data, before the transaction runs It must make sure that its changes leave the integrity constraints still holding However, there are allowed to be intermediate states where the constraints do not hold A transaction that does this, is called consistent This is an obligation on the programmer Usually the organization has a testing/checking and sign-off mechanism before an application program is allowed to get installed in the production system
Need for application rollback Previous sta...