Locks
X Lock : eXclusive Lock (single owner access)
S Lock : Shared Lock (multi owner access)
READ UNCOMMITTED
Write places X locks on rows
Read requests no locks, and so can read the uncommitted result of an independent transaction and so never blocked
Because it can read uncommitted data, which can be rolled back, it may read inconsistent data.
READ COMMITTED (default level)
Write places x locks on rows
Read checks for data being in another transaction and blocks until it is committed
by placing S locks on rows read but released at the end of the statement.
Note a new row after the query but before the commit of the transaction inserted by another transaction
will not be locked.
Two identical queries in a single transaction may receive different results.
REPEATABLE READ
S locks on rows are held till the end of the transaction.
SERIALIZABLE
Places range locks on indexes for the criterion in queries so that:
repeated identical queries in a single transaction always return the same result
and another transaction cannot modify the result e.g. by inserting a new row
SNAPSHOT
Effectively a form of repeatable read, achieved by the database keeping previous versions of rows
that are updated during the transaction.
There are two forms, the default mechanism uses the snapshot of the data current at the point the transaction starts, and the “read committed’ version, that uses the version current at the point the statement within the transaction executes.
It is specified as the isolation level as READ COMMITTED, ie. the default, but is set as a database option, that changes
the way the default isolation level is implemented.