Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Transactions, Introduction

Motivated by two independent requirements:
Concurrent database access
Resilience to system failures

Concurrent Access: Attribute-level Inconsistency
Update College Set enrollment = enrollment + 1000
Where cName = ‘Stanford’
concurrent with
Update College Set enrollment = enrollment + 1500
Where cName = ‘Stanford’

Concurrent Access: Tuple-level Inconsistency
Update Apply Set major = ‘CS’ Where sID = 123
concurrent with
Update Apply Set decision = ‘Y’ Where sID = 123

Concurrent Access: Table-level Inconsistency
Update Apply Set decision=’Y’
Where sID In (Select sID From Student Where GPA > 3.9)
concurrent with
Update Student Set GPA=(1.1)*GPA Where sizeHS > 2500

Concurrent Access: Multi-statement Inconsistency
Insert Into Archive
Select * From Apply Where decision=’N’;
Delete From Apply Where decision=’N’;
concurrent with
Select count(*) From Apply;
Select count(*) From Archive;

Concurrency Goal: Execute sequence of SQL statements so they appear to be running in isolation.
simple solutionL execute them in isolation
But we want to enable concurrency whenever safe to do so

System Failure Goal
Guarantee all-or-nothing execution, regardless of failures

Solution for both concurrency and failures: TRANSACTIONS
A transaction is a sequence of one or more SQL operations treated as a unit
Transactions appear to run in isolation
If the system fails, each transaction’s changes are reflected wither entirely or not at all
SQL Standard:
Transaction begins automatically on first SQL statement
On “commit” transaction ends and new one begins
Current transaction ends on session termination
“Autocommit” turns each statement into transaction

November 28, 2011 | Comments Off on Intro to Databases: Transactions, Introduction | Categories: Posts | Permalink

Comments are closed.