CS457 Transactions

1

• CS457 Transactions
• Chapter 17.1-17.3

2
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

3
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

4
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

5
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)

6
ACID property

• Atomicity
• Consistency
• Isolation
• Durability

7
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

8
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

9
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.

10
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

11
Durability

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

12
Concurrent access

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

13
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

14
Solutions

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

15
Locks

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

16
Locks contd

• 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

17
    Locks contd
T1 read-lock (Y) read-item (Y) unlock
(Y) write-lock (X) read-item (X) XXY write-
item (X) unlock (X)
T2 read-lock (X) read-item (X) unlock
(X) write-lock (Y) read-item (Y) YXY write-
item (Y) unlock (Y)
Y unlocked too early
X unlocked too early
Cannot serialize T1 and T2
18
Two-Phase Lock (2PL)

• 2PL is a solution that
• Request Lock before read or write
• Wait until no conflict
• Growing phase - request locks
• Shrinking phase - release locks
• Once any lock is released, cannot request
  another lock
• Commercial systems usually release all locks
  when transaction commits

19
Problems with 2PL?

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

20
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

21
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.

22
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

23
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) 

24
Transactions in Oracle 

• COMMIT WORK RELEASE makes all changes so 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

25
Summary
To improve performance
Interleave transactions
Correctness ACID
Serial schedule is correct
Serializable schedule is equivalent to some
serial schedule
Concurrency control enforces serializability

• 2PL
• Deadlock
• Granularity

Other strategies Not covered here
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
Triggers

• Suppose you want to make sure values you insert
  are correct (e.g. age gt 0)
• You can define a trigger to perform an action
  when a specific event occurs
• That event can be an insert, delete, update

28
Create Trigger

• CREATE TRIGGER trigger_name BEFORE AFTER
• INSERT DELETE UPDATE OF colname ,
  colname...
• ON tablename REFERENCING corr_name_def ,
  corr_name_def...
• FOR EACH ROW FOR EACH STATEMENT
• WHEN (search_condition)
• statement -- action (single statement)
• BEGIN ATOMIC statement statement... END
  
• -- action (multiple statement.)
• DROP TRIGGER trigger_name

29

• The corr_name_def in the REFERENCING clause looks
  like
• OLD ROW AS old_row_corr_name
• NEW ROW AS new_row_corr_name
• OLD TABLE AS old_table_corr_name
• NEW TABLE AS new_table_corr_name

30
Example

• Trigger example using PL/SQL (Oracles procedural
  language in SQLPlus)
• create trigger tr1 after insert on works_on
• referencing new x
• for each row when (x.hours gt 40)
• begin raise_application_error(-20003,'invalid
  hours on insert')
• end
• Use / to execute

31
Metadata

• To get information about a specific table
• Describe table_name
• Lists all attributes and type
• To get information about all user tables, can
  query user_tables
• Select table_name from user_tables

32
System tables

• user_tables
• user_tab_columns
• user_constraints
• user_cons_columns
• user_triggers
• user_views
• user_tab_privs
• user_tab_privs_made (lists privileges granted to
  others)
• user_col_privs