Chapter Twenty Two DATABASE TRANSACTIONS:

1
Chapter Twenty Two DATABASE TRANSACTIONS

• Objectives
• - Controlling transactions
• - Advantages of COMMIT and ROLLBACK
• - Read Consistency
• - Locks

2
Database Transactions

• DML
• DDL
• DCL
• Transaction starts with an execution of SQL
  statements and ends with one of the following
• COMMIT or ROLLBACK
• DDL or DCL (automatic commit)
• user exits
• system crashes

3
Controlling Transactions
Transaction
Point B
Point A
COMMIT
Rollback
Rollback
Rollback
Rollback
4
Controlling Transactions

• An automatic commit occurs under the following
  circumstances
• DDL statement is issued
• DCL statement is issued
• normal exit from SQL, without explicitly issuing
  COMMIT or ROLLBACK
• Abnormal Termination

5
Controlling Transactions

• AUTOCOMMIT can be set to ON or OFF
• SHOW AUTOCOMMIT

6
State of Data Before COMMIT or ROLLBACK

• The previous state of the data can be recovered
• The current user can review the result of the DML
  operation by using SELECT
• Other user cannot view the result of the DML
  statements by the current user
• The affected rows are locked (other users can not
  change the data within the affected rows)

7
State of the Data after COMMIT

• Data changes are made permanent in DB
• The previous state of the data is permanently
  lost
• All users can view the results
• Locks are released
• All saved points are erased

8
Committing Data

• UPDATE student
• SET majorCOSC
• WHERE id111
• COMMIT

9
State of the Data after ROLLBACK

• Data changes are undone
• Previous state of the data is restored
• Locks on the rows are released
• DELETE FROM student
• 10,000 record is deleted
• ROLLBACK

10
Roll Back Changes to a Marker

• Create a marker in a current transaction by
  using
• SAVEPOINT statement
• Rollback to that marker by using
• ROLLBACK TO statement
• UPDATE faculty SET salary100000 WHERE id111
• SAVEPOINT to_here
• INSERT INTO faculty .
• ROLLBACK TO to_here

11
Read Consistency

• Db users make two types of access to db
• read operations (SELECT)
• write operations (INSERT, UPDATE, DELETE)

12
Read Consistency

• Facts
• Db reader and writer are ensured a consistent
  view of the data
• Writers are ensured that the changes to the db
  are done in a consistent way
• Changes made by one writer do not disrupt or
  conflict with changes another writer is making

13
Locking

• What are locks
• Require no user action
• Implicit locking occurs for all SQL statements
  except SELECT
• Automatically use the lowest level of restrictive
  ness
• Are held for the duration of the transaction
• Have two basic modes
• -Exclusive
• -Shared