Transaction
isolation levels are a measure of the extent to which transaction isolation
succeeds. In particular, transaction isolation levels are defined by the
presence or absence of the following phenomena:
·
Dirty Reads A dirty
read occurs when a transaction reads data that has not yet been
committed. For example, suppose transaction 1 updates a row. Transaction 2
reads the updated row before transaction 1 commits the update. If transaction 1
rolls back the change, transaction 2 will have read data that is considered
never to have existed.
·
Nonrepeatable Reads A nonrepeatable
read occurs when a transaction reads the same row twice but gets
different data each time. For example, suppose transaction 1 reads a row.
Transaction 2 updates or deletes that row and commits the update or delete. If
transaction 1 rereads the row, it retrieves different row values or discovers
that the row has been deleted.
·
Phantoms A phantom is
a row that matches the search criteria but is not initially seen. For example,
suppose transaction 1 reads a set of rows that satisfy some search criteria.
Transaction 2 generates a new row (through either an update or an insert) that
matches the search criteria for transaction 1. If transaction 1 reexecutes the
statement that reads the rows, it gets a different set of rows.
Types
of protocols
a) Lock Based Protocol
1) Binary Lock
2) Shared/Exclusive Lock
b) Timestamp based
Protocol
Binary Lock- Any number
of transactions can perform READ operation. No point of locking the row
Exclusive
Lock – Some transaction if want to perform writer operation thus
exclusive lock.
