CS457 Transactions

1

• CS457 Transactions
• Chapter 17.1-17.3

2
The Computer Science Department, CS Advisory
Board, and ACM Student Chapterpresent
Mike ThomasCIO, Gulf States Paper Computer
Science in Heterogenous, Multidimensional
Business Environments 500-600 PM, Tuesday,
Dec. 2nd, EE 119 Pizza drinks served.
3
Transactions

• A transaction is
• a logical unit of work
• a sequence of steps to accomplish a single task
• Can have multiple transactions executing at the
  same time

4
Example

• Suppose we have a database containing 4
  relations
• Employees, Computer, Software packages, PCs
• Where PC is the relationship between Employee and
  Computer 
• Employees contains a field with the total value
  of the software installed on his/her PC (softval)
• Software contains the number of instances
  (numinst) of each package installed

5
Example contd 

• If a user wants to add a new PC for an employee
  the following steps must be done
• add a tuple to Computer
• add cid and eid to PC
• increment softval column in Employee
• increment numinst of SW for all SW installed

6
Problems

• What if the softval is updated, but the numinst
  is not incremented?
• Should complete all of the above steps or none
• How to indicate all of the above is considered to
  be a single transaction?
• Begin Transaction
• End Transaction statements (see later
  text for Oracle info)

7
ACID property

• Atomicity
• Consistency
• Isolation
• Durability

8
Atomicity  

• Atomicity A transactions change to the state
  are atomic either all happens or none happens.
• If a problem occurs before the end of a
  transaction, DBMS undoes all changes since begin
  transaction
• abort the transaction (rollback)
• If the transaction can be completed,
• commit the transaction

9
Atomicity contd

• Once a transaction is committed, any user
  accessing the DB should see new changes
• If a transaction is aborted, no one sees any
  changes

10
Consistency

• Consistency A transaction is a correct
  transformation of the state. The action taken
  as a group does not violate any of the integrity
  constraints associated with the state. This
  requires that the transaction be a correct
  program.

11
Isolation

• Isolation Even though transactions execute
  concurrently, it appears to each transaction, T,
  that others executed either before T or after T,
  but not both

12
Durability

• Durability Once a transaction completes
  successfully (commits), its changes to the state
  survive failures.

13
Concurrent access

• Suppose 2 users are updating the same employee
  record (Bob)
• Tom adds 50 worth of SW
• Mary deletes 50 worth of SW

14
Lost update

• First, both read in Bobs current softval of
  500
• Tom adds 50, writes softval 550
• Mary deletes 50, writes softval 450
• 
  or
• Mary deletes 50, writes softval 450
• Tom add 50, writes softval 550
• Either way, we have a lost update! The result
  should be 500

15
Solutions

• Permit only 1 transaction to update at a time
• Can still allow multiple retrievals (reads)
• How is this done?

16
Locks

• Lock employee's record until Tom updates, (Mary
  must wait)  
• Release locks,
• Mary acquires lock and then can update

17
Locks

• Two kinds of locks
• shared lock (read-lock)
• exclusive lock (write-lock)
• multiple share locks can be assigned
• only one exclusive lock can be assigned and it
  conflicts with a share lock
• only one person can be updating the data, 
• but if no one is updating, multiple readers can
  read the data

18
    Locks 

• Suppose you want to read employee with id 3
• If the system locks that tuple, you update it,
  then release the lock, 
• what happens if you want to read that tuple
  again?  
• Someone else may have changed its value.
•  
• Solution - a transaction should hold a lock
  until it is committed (or aborted)

19
Two-Phase Locking (2PL)

• 2PL is a solution that has
• Growing phase - request locks
• Shrinking phase - release locks
• Once any lock is released, cannot request
  another lock on the same or a different data item
• Commercial systems usually release all locks
  when transaction commits

20
Problems with 2PL?

• Suppose T1 updates Employee table and has it
  locked
• Suppose T1 wants to update the Software table and
  requests the lock
• Suppose T2 updates the Software table and has it
  locked
• Suppose T2 wants to update the Employee table and
  requests lock 
• both are waiting for tables the other one has
  locked -- Deadlock!

21
Solutions

• Can prevent deadlock by
• locking all data will need at beginning
• locking tables in a specific order
• detect if deadlock has occurred (use a
  waits-for-graph or a time-out) and choose to
  abort one of the transactions

22
Two common types of 2PL

• Conservative 2PL each transaction predeclare
  its readset and writeset.
• Strict 2PL all of a transactions locks are
  release only after it commits or aborts.

23
Lock granularity

• Lock granularity can have an impact on
  concurrency
• table, block or page, record, field
• the larger the granule, the easier, but less
  concurrency
• usually it is page level 
• Oracle
• doesn't lock for select
• uses graphs and time-out  
• locking granularity is row level

24
Transactions in Oracle 

• Transaction begins with first executable SQL
  statement.
• Transaction ends
• with commit, rollback (with or without release
  option).
• with an alter, create or grant (issues automatic
  commit before and after executing).
• with system failure or session stop unexpectedly
  (rollback) 

25
Transactions in Oracle 

• COMMIT WORK RELEASE makes all changes thus far in
  transaction permanent 
• ROLLBACK WORK RELEASE undoes all changes in
  transaction
• Can set a savepoint to rollback to
• SAVEPOINT savepoint_name that can 
• ROLLBACK TO savepoint_name

26
Misc. topic Triggers

• Active database
• Triggers - specifying types of active rules
• Event-condition-action
• Event is typically insert, update
• Condition determines whether rule action should
  be executed
• Action to be taken

27
Trigger example using PL/SQL (Oracles procedural
language)

• Rule 1 Create trigger totalsal1
• After insert on employee
• For each row
• When (new.dno is not null)
• update department
• set total_sal total_sal new.salary
• where dnonew.dno