There are three types of locks:
* shared
* exclusive
* update
shared
These locks are requested and used by readers of information. More than one connection can hold a shared lock on a data page.
This allows for multiple readers.
exclusive
The SQL Server uses exclusive locks when data is to be modified. Only one connection may have an exclusive lock on a given data page. If a table is large enough and the data is spread sufficiently, more than one connection may update different data pages of a given table simultaneously.
update
A update lock is placed during a delete or an update while the SQL Server is hunting for the pages to be altered. While an update lock is in place,there can be shared locks thus allowing for higher throughput.
The update lock(s) are promoted to exclusive locks once the SQL Server is ready to perform the delete/update.
Table Locks
There are three types of table locks:
* intent
* shared
* exclusive
intent
Intent locks indicate the intention to acquire a shared or exclusive lock on a data page. Intent locks are used to prevent other transactions from acquiring
shared or exclusive locks on the given page.
shared
This is similar to a page level shared lock but it affects the entire table.This lock is typically applied during the creation of a non-clustered index.
exclusive
This is similar to a page level exclusive lock but it affects the entire table.If an update or delete affects the entire table, an exclusive table lock is generated. Also, during the creation of a clustered index an exclusive lock is generated.
Demand Locks
A demand lock prevents further shared locks from being set. The SQL Server sets a demand lock to indicate that a transaction is next to lock a table or a page.
This avoids indefinite postponement if there was a flurry of readers when a writer wished to make a change.
2.Demand Locks
Demand locks prevent any more shared locks from being set. SQL Server sets a demand lock to indicate that a transaction is next in line to lock a table or page. This avoids situations in which read transactions acquire overlapping shared locks, monopolizing a table or page, so that a write transaction waits indefinitely for its exclusive lock.
After waiting on several different read transactions, SQL Server gives a demand lock to the write transaction. As soon as the existing read transactions finish, the write transaction is allowed to proceed. Any new read transactions must then wait for the write transaction to finish, when its exclusive lock is released.
3.What are the Isolation levels in Transaction
The SQL92 standard defines four levels of isolation for transactions. Each isolation level specifies the kinds of actions that are not permitted while concurrent transactions are executing. Higher levels include the restrictions imposed by the lower levels:
Level 0 - ensures that data written by one transaction represents the actual data. It prevents other transactions from changing data that has already been modified (through an insert, delete, update, and so on) by an uncommitted transaction. The other transactions are blocked from modifying that data until the transaction commits. However, other transactions can still read the uncommitted data, which results in dirty reads.
Level 1 - prevents dirty reads. Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid. This is the default isolation level supported by Adaptive Server.
Level 2 - prevents nonrepeatable reads. Such reads occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield different results than the original read.
Adaptive Server supports this level for data-only-locked tables. It is not supported for allpages-locked tables.
Level 3 - ensures that data read by one transaction is valid until the end of that transaction, hence preventing phantom rows. Adaptive Server supports this level through the holdlock keyword of the select statement, which applies a read-lock on the specified data. Phantom rows occur when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies the data (through an insert, delete, update, and so on). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows.
You can set the isolation level for your session by using the transaction isolation level option of the set command. You can enforce the isolation level for just a query as opposed to using the at isolation clause of the select statement. For example:
set transaction isolation level 0
Default isolation levels for Adaptive Server and SQL92
By default, the Adaptive Server transaction isolation level is 1. The SQL92 standard requires that level 3 be the default isolation for all transactions. This prevents dirty reads, nonrepeatable reads, and phantom rows. To enforce this default level of isolation, Transact-SQL provides the transaction isolation level 3 option of the set statement. This option instructs Adaptive Server to apply a holdlock to all select operations in a transaction. For example:
set transaction isolation level 3
Applications that use transaction isolation level 3 should set that isolation level at the beginning of each session. However, setting transaction isolation level 3 causes Adaptive Server to hold any read locks for the duration of the transaction. If you also use the chained transaction mode, that isolation level remains in effect for any data retrieval or modification statement that implicitly begins a transaction. In both cases, this can lead to concurrency problems for some applications, since more locks may be held for longer periods of time.
To return your session to the Adaptive Server default isolation level:
set transaction isolation level 1
