Week 22 CST 363 Intro to Database Systems

·      Know what are ACID features of a database and why are they important

o  Atomicity

§  Transactions are all or nothing

o  Consistency

§  Only valid data is saved

o  Isolation

§  Transactions do not affect each other

§  locks

o  Durability

§  Written data will not be lost

·      Know that locks, versioning and optimistic locking are used to achieve ACID properties of concurrent transactions

o  Concurrency is the ability of the system to support two or more transactions working with the same data at the same time. Concurrency problems are prevented using locks which stop other transactions from executing if another is already running.

·      Know how to begin and terminate transactions in application code and SQL scripts 

o  Use START TRANSACTION to turn off autocommit mode

o  Use COMMIT to commit the changes

o  Use ROLLBACK to roll back the changes

·      Use the correct ANSI standard isolation level to make trade-off between concurrency and ACID properties

o  

o  The simplest way to prevent concurrency problems is to change the default locking by SET TRANSACTION ISOLATION LEVEL

·      Use SESSION for the session

·      USE GLOBAL for all sessions

§  Use SERIALIZABLE isolation level to isolate it from every other transaction and restrict concurrency, every resource is locked and transactions cannot access it

·      it requires more overhead to manage the locks

§  Use READ UNCOMMITTED isolation level by just using SELECT queries and not setting any locks

§  Use READ COMMITTED to prevent transactions from seeing data that has been changed by other transactions but  not committed

§  Use REPEATBLE READ to be read consistently in the same transaction

·      Know how rollback and forward recovery work in a database

o  Use SAVEPOINT to create a savepoint with a specific name

o  USE ROLLBACK TO SAVEPOINT to roll back to a specified save point

·      MySQL supports and uses both locks and MVCC

·      MVCC is used so that reader will not be blocked by a writer holding a lock on a row

o  row locking is used so that multiple writers must serialize when updating the same row

·      A transaction that reads a row with the intention of later updating the row should issue  SELECT ... WITH UPDATE  to acquire a lock on a row when the row is read.  This prevents another transaction from modifying the row between the time of select and time of update

·      InnoDB Transaction Model       

o  combines the best of multi-versioning database with two-phase locking at the row level and run queries

o  The lock info is stores so lock escalation is not needed