10. transaction concurrency

transaction

A logical unit of work that must be entirely completed or aborted

what is a database request

the equivalent of a single SQL statement in an application program or transaction.

atomicity

all operations of a transaction are completed; if not aborted

consistency

Permanence of database’s consistent state

isolation

Data used during transaction cannot be used by second transaction until the first is completed

durability

Ensures that once transactions are committed they cannot be undone or lost

serializability

Ensures that the schedule for the concurrent execution of several transactions should yield consistent results

single user database systems

• serializability and isolation: automatially maintained

• ACID: must guarantee Atomicity, Durability, and Consistency

• error recovery: Manage recovery from operating system errors, power interruptions, and application crashes.

multiuser database systems

• concurrent transactions: Typically handle multiple transactions at the same time

• control mechanism: Implement controls to ensure serializability and isolation.

• ACID: maintain atomicity, durability, consistency, and isolation

transaction interference

risks when concurrent transactions interact with the same dataset

update conflicts

risks when potential inconsistency if one transaction updates data before another completes

concurrency control

Coordination of the simultaneous transactions execution in a multiuser database system

lost update

A situation where a transaction updates a data item after another transaction has read it, resulting in the last update overwriting changes made by the first without being aware.

uncommitted data

occurs when two transactions happen at the same time and the first transaction is rolled back after the second one has already accessed uncommitted data

inconsistent retrievals

A transaction accesses data before and after one or more other transactions finish working with such data

serializable schedule

interleaved execution of transactions yields the same results as the serial execution of the transactions

locking methods do..

facilitate isolation of data items used in concurrently
executing transactions

pessimistic locking

use of locks based on the assumption that conflict between transactions is likely

binary lock (locked)

• If an object is locked by a transaction, no other transaction can use that object

binary lock (unlocked)

• If an object is unlocked, any transaction can lock the object for its use

exclusive lock

Access is reserved for the transaction that locked the object

shared lock

Concurrent transactions are granted read access on the basis of a common lock

problems with locks

affect serializability and the schedule might create deadlocks

two phase locking does not do what?

ensure deadlocks

two phase locking does what?

ensure serializability

growing phase

transaction acquires all required locks without unlocking any data

shrinking phase

transaction releases all locks and cannot obtain any new lock

two transactions cannot have

two of the same locks

no unlock operation can..

precede a lock operation in the same transaction

for locking, no data are effected until

all of the locks are obtained

deadlocks

occur when two transactions are waiting for the other to unlock data (deadly embrace)

concurrency control with time stamps

• ensures no equal time stamp values exist

• Monotonicity: ensures time stamp values always increases

disadvantages of time stamping

• more space and resources

• each value store in database needs two more time stamp fields

• more processing

wait/die

A concurrency control scheme in which an older transaction must wait for the
younger transaction to complete and release the locks before requesting the locks itself

wound/wait

A concurrency control scheme in which an older transaction can request the lock, preempt the younger transaction, and reschedule it

optimistic methods

based on the assumption that the majority of database operations do not conflict

read phase

Reads the database
- Executes the needed computations
- Makes the updates to a private copy of the database values

validation phase

Transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database

write phase

Changes are permanently applied to the database

transaction isolation levels

refer to the degree to which transaction data is “protected or isolated” from other concurrent transactions

transaction isolation levels are described as..

by the type of “reads” that a transaction allows or not

dirty read

transaction can read data that is not yet committed

nonrepeatable read

transaction reads a given row at time t1, and then it reads the same row at time t2, yielding different results

phantom read

transaction executes a query at time t1, and then it runs the same query at time t2, yielding additional rows that satisfy the query

(ANSI) Read uncommitted

• reads uncommitted data from other transactions

• increases transaction performance but at cost of consistency

(ANSI) Read Committed

• forces transactions to read only committed data

• default for most databases

(ANSI) repeatable read

• ensures that queries return consistent results

• Uses shared locks to ensure other transactions do not update a row after the original query reads it

(ANSI) Serializable isolation level

• the most restrictive level defined by the ANSI SQL
  standard

recovery transactions are based on

atomic transaction property

write ahead log protocol

Ensures that transaction logs are always written before the data are updated

Redundant transaction logs

Ensure that a physical disk failure will not impair the DBMS’s ability to recover data

buffers

Temporary storage areas in a primary memory used to speed up disk operations

checkpoints

Allows DBMS to write all its updated buffers in memory to disk

Deferred-write technique or deferred update

Transaction operations do not immediately update the physical database
- Only transaction log is updated

Write-through technique or immediate update

Database is immediately updated by transaction operations during transaction’s execution