There are
multiple modes for data that is locked due to transactions occurring in other
sessions. One mode is a shared lock mode. This happens when a SELECT Statement
is reading data. Another is an exclusive lock mode, where changes are occurring
in some other transaction. In this second scenario, other changes to the data
that is locked are blocked. Any other sessions will need to wait until the
first transaction is ended. If multiple different items are locked between more
than 1 session, a deadlock scenario can occur where neither session can gain
locks on data they need to change in order to complete.
There are
settings that control how the database can respond to locking. These are called
the isolation levels. They are set on a session basis. The default level is
read commited. Other sessions can only read data once changes are commited to
the database. Another level is read uncommitted, where other sessions can read
the changes made in the transaction of another session. There is some danger
here, as the changes could later be rolled back.
There is still another level of isolation called
read committed snapshot. It is also known as TCSI. This is a variation of read
committed. Other sessions can proceed to read values from a snapshot of the
version of data prior to a transaction making changes. As an aside, this is the
default level used in the cloud Azure database.
