Chapter 10 Update Transactions

1
Chapter 10 Update Transactions

• caili
• caili_at_ynu.edu.cn

2
Update Transaction

• DEFINITION 10.1 Transaction
• A transaction is a means by which an application
  programmer canpackage together a sequence of
  database operations so that the database system
  can provide a number of guarantees, known as the
  ACID properties of a transaction.
• When the operations making up a transaction
  consist of both readsand updates, they represent
  an attempt by the application programmer to
  perform a consistent change of state in the data
  when the operations of a transaction consist only
  of reads, they represent an attempt at a
  consistent view of the data.

3
Update Transaction

• Starting in the 1950s, transactions were
  developed to solve a number of problems that
  early systems designers faced in writing large
  database applications. In dealing with these
  applications, early developers faced the
  following problem
• Creating an inconsistent result.
• Errors of concurrent execution.
• Uncertainty as to when changes become permanent

4
Update Transaction

• To solve these problems, the systems analysts
  came up with the concept of a transaction.
• The database system makes the following four
  transactional guarantees, known as the ACID
  guarantees, or ACID properties.
• The term ACID is an acronym for four properties
  Atomicity, Consistency, Isolation, and Durability.

5
Update Transaction

• Atomicity
• This property guarantees that a set of record
  updates that are part of a transaction is
  indivisible. Thus either all updates of a
  transaction occur in the database, or none of
  them occurs.
• Consistency
• Complete transactional transformations on data
  elements bring the database from one consistent
  state to another.

6
Update Transaction

• Isolation
• That transactions are isolated means that one can
  only affect another as it would if they were not
  concurrent, that is, if their operations were not
  interleaved in time.
• Another name for this property is
  serializability, meaning that any schedule of
  interleaved operations permitted by the system is
  equivalent to some serial schedule, where
  transactions are scheduled one at a time, and all
  operations of one transaction complete before any
  operation of another transaction can start.

7
Update Transaction

• Durability
• The last property guaranteed by the system is
  this when the system returns to the program
  logic after a Commit Work statement, the
  transaction is guaranteed to be recoverable.

8
10.1 Transaction Histories

• The need for the transaction isolation concept is
  present whenever two or more users can perform
  interleaved operations of read and write on the
  database.
• To "read" means to access a data item, such as an
  individual row of a table or an index entry in
  the database to "write" means to change or
  update a data item in the database.

9
10.1 Transaction Histories

• Fundamental Atomic Read and Write Actions in the
  Database
• We use the notation Ri(A) to mean that a
  transaction, given an identification number i by
  the database system and denoted by Ti, performs a
  read of data item A.
• Consider a table T1 with two columns, uniqueid
  and val. Then Ri(A) can usually be pictured as
  transaction Ti performing the following SQL
  statement
• select val into pgmval1 from T1 where
  uniqueid A
• We also use the notation Wj(B) to mean that a
  transaction Tj performs a write on data item B,
  and this can be pictured as transaction Tj
  performing the following SQL statement
• update T1 set val pgmval2 where uniqueid B

10
10.1 Transaction Histories

• Fundamental Atomic Read and Write Actions in the
  Database
• We say that the Ri(A) and Wj(B) actions are
  atomic, meaning that they can be pictured as
  happening in an instant between all other actions
  in the database. This is not quite the same thing
  as the atomicity of transactions that was
  mentioned above.
• Note that we are not specifying the actual values
  read and written with the notation Ri(A) and
  Wj(B) but if we wish we can expand Ri(A) to be
  Ri(A, val1) and Wj(B) to be Wj(B, val2).

11
10.1 Transaction Histories

• Predicate Read Actions
• Assume that transaction T1 were to execute the
  SQL statement on a set of rows
• 10.1.1 update tbl set val 1.15val
• where uniqueid between low and high
• The first operation performed by T1 would be a
  predicate read, R1(PREDICATE), meaning that the
  set of rows satisfying the predicate in the WHERE
  clause, with uniqueid values between the program
  variables low and high, must be determined by
  some means, such as an index lookup.

12
10.1 Transaction Histories

• Predicate Read Actions
• The Update statement 10.1.1 running under
  transaction T1 would be denoted as follows
• R1(predicate uniqueid between low and
  high)
• After taking note of the list of rows that fall
  in the given range, the Update statement of
  10.1.1 will perform a sequence of read-write
  actions, R1(uniqueidk, valuek) followed by
  W1(uniqueidk, 1.15 valuek), for all uniqueidk
  values in the low to high range.

13
10.1 Transaction Histories

• Transactional Histories with Reads and Writes
• For now, we assume that all database activity
  performed by any transaction can be modeled as a
  series of reads (Ri(A)) and writes (Wj(B)),
  together with commits (Ci) arising from the
  Commit Work statement and aborts (Aj) arising
  because of the Rollback Work statement or because
  of deadlock,
• An "interleaved" series of read and write
  operations performed by two transactions, T1 and
  T2, might look like this
• 10.1.2 . . . R2(A) W2(A) R1(A) R1(B) R2(B)
  W2(B) C1 C2 . . .
• A sequence of operations such as this is known as
  a transactional history, or sometimes a
  schedule.

14
10.1 Transaction Histories

• Transactional Histories with Reads and Writes
• This history results from a series of calls
  submitted by simultaneously executing
  transactions at the application program level
  (see Figure 10.1) and eventually transformed into
  the form of 10.1.2 at the level of the database
  scheduler.
• Here is a restatement of the history of 10.1.2
  with the given value assumptions added.
• 10.1.3 . . . R2(A, 50) W2(A, 20) R1(A, 20)
  R1(B, 50) R2(B, 50) W2(B, 80) C1, C2. . .

15
10.4 Locking to Ensure Serializability

• It is the job of the scheduler to ensure that the
  "interleaved history" of all the transactional
  operations is serializable. It does this by
  forcing some transactional operations to WAIT and
  allowing others to proceed, so that the resulting
  history of operations is a serializable one.
• One simple approach we have already mentioned can
  be used by the schedulerto impose a strict
  serial history discipline on the transactional
  operations, insisting that all operations of one
  transaction be complete (including a final commit
  or rollback), before operations from the next
  transaction are allowed to begin.
• Another approach is locking that guarantee a
  serializable schedule with a good deal of
  interleaving of operations.

16
10.4 Locking to Ensure Serializability

• The locking discipline used to assure
  transactional consistency in commercial database
  systems is known as two-phase locking,
  abbreviated 2PL.
• DEFINITION 10.4.1 Two-Phase Locking, or 2PL.
• 1 When transaction Ti attempts to read a data
  item, Ri(A), the scheduler intercepts this call
  and first issues a call on its behalf to read
  lock the data item, RLi(A). Similarly when Ti
  attempts to write (update) a data item, Wi(A),
  the scheduler first issues a call on its behalf
  to write lock the data item, WLi(A).
• 2 Before granting a lock on a data item, the
  scheduler requires the requesting transaction to
  WAIT until no conflicting lock on the data item
  exists.
• Two locks on the same data item are said to
  conflict if and only if they are attempted by
  different transactions and at least one of the
  two locks is a write lock.

17
10.4 Locking to Ensure Serializability

• 3 There are two phases to locking the growing
  phase, during which locks are acquired, and the
  shrinking phase, during which locks are released.
  The scheduler must ensure that after the
  shrinking phase begins, no new locks are
  acquired it is forbidden for a transaction to
  release a lock and then acquire another lock at a
  later time.
• THEOREM 10.4.2 Locking Theorem.
• A history of transactional operations that
  followsthe 2PL discipline is always
  serializable.

18
10.5 Level of Isolation

• Theorem 10.4.2 tells us that a scheduler can
  guarantee serializable histories by imposing a
  two-phase locking discipline.
• Property 3 of 2PL assures us that a transaction
  won't release a lock and later acquire a
  different lock, so we have to hold all locks
  until there are no remaining new data item
  accesses to come. In fact, we are assuming that
  we hold all locks taken by a transaction until
  the transaction commits or aborts.
• Because of some problem, it was suggested a
  number of years ago that database system
  schedulers might weaken the rules of two-phase
  locking to reduce the number of transactional
  access conflicts that cause WAITs. This feature
  is known as isolation levels.

19
10.5 Level of Isolation

• Here are the four isolation levels provided by
  SQL-99, in increasing restrictiveness and level
  of isolation guarantee to the programmer
• 1 Read Uncommitted (sometimes known as the
  "dirty reads" isolation level)
• 2 Read Committed (a somewhat weaker form than
  DB2's "cursor stability")
• 3 Repeatable Read
• 4 Serializable

20
10.5 Level of Isolation

• The ANSI SQL-99 Set Transaction statement format
  follows
• SET TRANSACTION READ ONLY READ WRITE)
• ISOLATION LEVEL READ UNCOMMITTED READ
  COMMITTED REPEATABLE READ SERIALIZABLE
• A READ ONLY transaction cannot perform any
  updates as part of its execution (updates would
  result in an error message), whereas a READ WRITE
  transaction can.
• A short-term lock on a data item is one that is
  held just long enough to perform the access
  operation associated with the lock.
• A long-term lock means that the lock is held
  until the transaction commits.

21
10.5 Level of Isolation
Figure 10.9 Long-Term Locking Behavior of
SQL-99 Isolation Levels