SQL Isolation Levels 1 SQL Commands for Transactions

SQL Isolation Levels 1

SQL Commands for Transactions • • • START TRANSACTION COMMIT ROLLBACK SET TRANSACTION READ ONLY SET TRANSACTION READ WRITE – This is the default 2

Four Isolation Levels • SQL standard provides 4 isolation levels – Read uncommitted – Read committed – Repeatable read – Serializable 3

Read Uncommitted • Transaction can read rows that have been modified by other transactions but not yet committed Dirty Reads Possible Non-repeatable Reads Possible Phantom Reads Possible 4

Read Committed • Transaction cannot read rows that have been modified by other transactions but not yet committed Dirty Reads Not Possible Non-repeatable Reads Possible Phantom Reads Possible 5

Repeatable Read • Transaction cannot read rows that have been modified by other transactions but not yet committed • No other transactions can modify data that has been read by the current transaction until the current transaction completes Dirty Reads Not Possible Non-repeatable Reads Not Possible Phantom Reads Possible 6

Serializable • • Transaction cannot read rows that have been modified by other transactions but not yet committed No other transactions can modify data that has been read by the current transaction until the current transaction completes • Other transactions cannot insert new rows that would be returned by select statements in the current transaction until the current transaction completes Dirty Reads Not Possible Non-repeatable Reads Not Possible Phantom Reads Not Possible 7

Different Isolation Levels for Different Transactions • If all transactions run at isolation level “serializable” – equivalent to a serial running of transactions • Every transaction determines its level of isolation 8