How to determine isolation level

Running SQLServer2000 on Win2k. How can I determine the isolation level of
a running transaction?
Thanx.
Vince wrote:
> Running SQLServer2000 on Win2k. How can I determine the isolation
> level of a running transaction?
> Thanx.
READ COMMITTED is the default unless you specifically change it using
SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
statement itself. If the isolation level is overriden for the session,
you can see this using DBCC USEROPTIONS (look for the item called
"isolation level"). If the "isolation level" option is not there, it
means you're using the default value.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thank you for the response. Is there any way to determine the isolation
level of another transaction (not mine); thru Enterprise Manager, for
example?
Thanx again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:umXsW3l3FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Vince wrote:
> READ COMMITTED is the default unless you specifically change it using
> SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
> statement itself. If the isolation level is overriden for the session,
> you can see this using DBCC USEROPTIONS (look for the item called
> "isolation level"). If the "isolation level" option is not there, it
> means you're using the default value.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Vince wrote:
> Thank you for the response. Is there any way to determine the
> isolation level of another transaction (not mine); thru Enterprise
> Manager, for example?
There may be a way to pull that information from sysprocesses, but
nothing I can see directly. In any case, that won't tell you what
isolation level the user is currently using anyway for a given
transaction. Each DML statement could use a an isolation level different
that the user's default. What is the reason you are trying to determine
this information?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I've been asked by one of our developers if this can be done. He's working
with a third party front end that doesn't afford a lot of opportunity to
deal with isolation levels, but he was able to modify some of the Selects to
include a hint (read uncommited I think) and wanted to make sure that SQL
Server was actually taking the hint.
It's an interesting question and I don't work with SQL Server very much, so
I thought I'd open it up to the experts.
Thanx again for all your help.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OKfXA6v3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Vince wrote:
> There may be a way to pull that information from sysprocesses, but
> nothing I can see directly. In any case, that won't tell you what
> isolation level the user is currently using anyway for a given
> transaction. Each DML statement could use a an isolation level different
> that the user's default. What is the reason you are trying to determine
> this information?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Vince wrote:
> I've been asked by one of our developers if this can be done. He's
> working with a third party front end that doesn't afford a lot of
> opportunity to deal with isolation levels, but he was able to modify
> some of the Selects to include a hint (read uncommited I think) and
> wanted to make sure that SQL Server was actually taking the hint.
>
If the hint is in the query, then I think you can just assume SQL Server
is using it. However, READ UNCOMMITTED (which is the same as a NOLOCK
hint in the query and allows dirty reads) is only applicable for
SELECTs.
David Gugick
Quest Software
www.imceda.com
www.quest.com