Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

FILESTREAM and Database Transaction Isolation Levels

Apr 22 2013 12:00AM by Jacob Sebastian   

FILESTREAM data can be accessed by TSQL as well as through the Win32/Managed APIs. Accessing FILESTREAM data using TSQL is not recommended for a number of reasons such as the inability to stream data when reading or writing through TSQL. The recommended way to access FILESTREAM data is through the FILESTREAM APIs (Win32/Managed). There is a slight difference in behavior between TSQL and API behavior when accessing TSQL data under different transaction isolation levels. This post tries to examine the behavior of FILESTREA data access under different transaction isolation levels.

SQL Server 2008 only supports the following transaction isolation levels when working with FILESTREAM data.

  1. READ COMMITTED
  2. READ UNCOMMITTED
  3. REPEATABLE READ
  4. SERIALIZABLE

SQL Server 2008 does not allow SNAPSHOT isolation levels (SNAPSHOT and READ COMMITTED SNAPSHOT) on databases with FILESTREAM file groups. This limitation was removed in SQL Server 2008 R2. Both R2 and SQL Server 2012 allow accessing FILESTREAM data under the SNAPSHOT isolation levels.

  1. If the row being accessed is not locked by another process, FILESTREAM data can be accessed for reading or writing under all the transaction isolation levels listed above.
  2. If the row being accessed is locked (shared or exclusive mode) by another process, a write operation will fail under all the transaction isolation levels.
  3. If the row being accessed is locked in shared mode by another process, FILESTREAM data can be accessed for reading under all the transaction isolation levels. However, if the row is locked exclusively by another process, FILESTREAM data can be read only using the SNAPSHOT isolation level, and you will get the previous version of the FILESTREAM data value in case the other process has modified the FILESTREAM value.

READ COMMITTED, REPEATABLE READ and SERIALIZABLE isolation levels

The behavior of FILESTREAM data is the same for READ COMMITTED, REPEATABLE READ and SERIALIZABLE.

If the row that contains the FILESTREAM value that we want to read or write is exclusively locked by another process, a call to PathName() will be blocked. This will generate an error after the timeout duration is over. When using ADO.NET, you will see the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

It is quite possible that the row is not locked at the time of retrieving the PathName() value and the FILESTREAM transaction context. However, if another process obtains an exclusive lock on the row right after that and before the Win32 API call is made, the attempt to open the FILESTREAM data file for read or write operation will fail with the following message:

The process cannot access the file specified because it has been opened in another transaction.

If the row that contains the FILESTREAM value that we are trying to access is locked in SHARED mode, a call to PathName() will succeed. In such a case, FILESTREAM data will be available for reading. However, if you attempt to write to the FILESTREAM data file, the operation will fail with the following message:

The process cannot access the file specified because it has been opened in another transaction.

READ UNCOMMITTED isolation level

Under the READ UNCOMMITTED isolation level, a call to PathName() returns NULL if the row is exclusively locked by another process, and neither read nor write operations can be performed on the FILESTREAM cell using Win32 API.

If there is a shared lock on the row, PathName() returns the correct value, and you can access the FILESTREAM data for reading. If you attempt to open the FILESTREAM data file for writing, SQL Server will generate the following error:

The process cannot access the file specified because it has been opened in another transaction

SNAPSHOT isolation level

Under the SNAPSHOT isolation level (available since SQL Server 2008 R2), a client application can read FILESTREAM values stored on rows that are exclusively locked by another process. Unlike the other transaction isolation levels, a call to PathName() is not blocked. If the other transaction has modified the FILESTREAM value, you will get the previous version of the FILESTREAM data file.

Note that SNAPSHOT isolation level is disabled by default. You can determine whether the SNAPSHOT isolation level is enabled on your database by running the following query.

SELECT
    snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME()
/*
snapshot_isolation_state_desc
------------------------------
OFF
*/

If the SNAPSHOT isolation level to be disabled (OFF), you can enable it by running the query the following query. Note that SQL Server 2008 does not allow you to enable SNAPSHOT isolation level on databases having FILESTREAM file groups.

ALTER DATABASE NorthPole
SET ALLOW_SNAPSHOT_ISOLATION ON

Under SNAPSHOT isolation level, you cannot open the FILESTREAM data file for write or read-write operations. If you attempt to do so, SQL Server will display the following error:

The process cannot access the file specified because it has been opened in another transaction.

Summary of FILESTREAM behavior under transaction isolation levels

The following table summarizes the behavior of FILESTREAM operations under various transaction isolation levels, when the row being accessed is locked by another process.

+-----------------+------------------+-----------+-----------+
|Row is locked by |                  |   T-SQL   |   Win32   |
|another process  | Isolation Level  |Read  Write|Read Write |
+-----------------+------------------+-----+-----+-----+-----+
|EXCLUSIVE        | READ COMMITTED   |No   |No   |No   |No   |
|EXCLUSIVE        | READ UNCOMMITTED |Yes  |No   |No   |No   |
|EXCLUSIVE        | REPEATABLE READ  |No   |No   |No   |No   |
|EXCLUSIVE        | SERIALIZABLE     |No   |No   |No   |No   |
|EXCLUSIVE        | SNAPSHOT         |Yes  |No   |Yes  |No   |
|SHARED           | READ COMMITTED   |Yes  |No   |Yes  |No   |
|SHARED           | READ UNCOMMITTED |Yes  |No   |Yes  |No   |
|SHARED           | REPEATABLE READ  |Yes  |No   |Yes  |No   |
|SHARED           | SERIALIZABLE     |Yes  |No   |Yes  |No   |
|SHARED           | SNAPSHOT         |Yes  |No   |Yes  |No   |
+-----------------+------------------+-----+-----+-----+-----+


The Art of SQL Server FILESTREAM

Tags: 


Jacob Sebastian
1 · 100% · 32235
2
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"FILESTREAM and Database Transaction Isolation Levels" rated 5 out of 5 by 2 readers
FILESTREAM and Database Transaction Isolation Levels , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]