SET TRANSACTION ISOLATION LEVEL cannot be executed in a UDF (SQL Server function), but can be applied with the use of TABLE HINTS.SQL Server Locking and row versioning are central to isolation levels
SET TRANSACTION ISOLATION LEVEL controls the locking levels and row versioning characteristics of DML statements.
Locking levels and row versioning methods are used to maintain integrity of transactions.
SQL Row versioning maintains different versions of rows during a transaction. During a transaction, rows from the transaction start are available. Row versioning decreases the possibility of locking.
Locking levels are applied at a row, page or table level. They are applied in a way that minimises problems for the current transaction. The relevant locks stop other transactions from accessing data, when the current transaction completes the locks are released.
An application can choose an isolation level – defining the levels of access another transaction has on the current data. The choice of isolation level defines the row versioning and locking levels
The isolation levels available are: READ UNCOMMITTED, READ COMMITTED, REPEATABLE
READ, SNAPSHOT, SERIALIZABLE
Firstly, only one isolation level can be set per connection – unless changed .
Secondly, a UDF can’t execute the SET TRANSACTION ISOLATION LEVEL. The alternative for a UDF is a table hint. The table hint overrides the optimizer choice for the duration of the DML. An example of a table hint is:
SELECT col1,col2 FROM myTable WITH (TABLOCK)
WHERE col1 = ‘a_value’
Locks on resources need to be managed effectively. An interesting by-product of locks is SQL Server SLEEPING MODE, locks and transactions
Although this restriction applies to a SQL Server UDF, the TABLE HINTS offer sufficient flexibility to achieve a similar approach
Republished from http://www.sqlserver-dba.com.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].