I have a classic ASP
page and want to set the transaction isolation level
to READ UNCOMMITTED
. Using this documentation I have came up with the following:
Set conn = GetConnection
conn.IsolationLevel = adXactReadUncommitted
'conn.BeginTrans
'conn.CommitTrans
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO [dbo].[A] ([IsolationLevel]) SELECT CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL FROM sys.dm_exec_sessions where session_id = @@SPID"
Set rs = cmd.Execute()
response.write(conn.IsolationLevel)
The last response.write
gives me correctly 256
(READ UNCOMMITTED
) but when I query the table I got only ReadCommitted
records.
Could anyone tell what I am doing wrong?
And here is the body of the GetConnection
function:
FUNCTION GetConnection()
DIM strConnectionDotNet : strConnectionDotNet = "Data Source=..."
SET GetConnection = Server.CreateObject("adodb.connection")
GetConnection.connectionstring="Provider=sqloledb;" & strConnectionDotNet
GetConnection.open
END FUNCTION
As it is said in the documentation:
It is a kind of strange, but I need to the following:
Even thought the
T-SQL
statement is aSELECT
. Also, after theconn.CommitTrans
it is still using the default isolation level (the one specified in the context of the database).