Detect isolation level (transaction type) in C/AL

mentat · 2008-09-11

Hello,
is it possible to detect current isolation level in C/AL?
I have this problem. CURRENTTRANSACTIONTYPE always return TRANSACTIONTYPE::UpdateNoLocks. However it doesn't say if it is still BROWSE isolation level (i.e. no INSERT, MODIFY, DELETE operations occur) or isolation level is already SERIALIZABLE.

kine · 2008-09-11

You mean "how to find out that write transaction was started?" I think there is no C/AL for that. But may be someone will find way around...

bbrown · 2008-09-11

You can use DBCC to get the current isolation level for the active connnection on SQL

EXAMPLE CODE:

SET NOCOUNT ON
CREATE TABLE #Options (
[Set Option] VARCHAR(50),
[Value] VARCHAR(50))

INSERT INTO #Options
EXEC ('DBCC USEROPTIONS WITH NO_INFOMSGS')

Select * from #Options
Where [Set Option] = 'isolation level'

DROP TABLE #Options
SET NOCOUNT OFF
GO

You could take this example and create a stored procedure. Then use ADO to call it from NAV.

kine · 2008-09-12

bbrown wrote:

You can use DBCC to get the current isolation level for the active connnection on SQL

EXAMPLE CODE:

SET NOCOUNT ON
CREATE TABLE #Options (
[Set Option] VARCHAR(50),
[Value] VARCHAR(50))

INSERT INTO #Options
EXEC ('DBCC USEROPTIONS WITH NO_INFOMSGS')

Select * from #Options
Where [Set Option] = 'isolation level'

DROP TABLE #Options
SET NOCOUNT OFF
GO

You could take this example and create a stored procedure. Then use ADO to call it from NAV.

But if you use ADO, it will create new connection... or not? And if yes, than you cannot use it to find transaction isol. level for current NAV session...

garak · 2008-09-12

ADO creates a new connection. And dbcc useroptions only returns the infos of the current session (and this is the ADO session). By the way. DBCC are supported in SQL 2000 / 2005, but in future version .......

Maybe you can create a stored procedure or function with an parameter like CurrSessionID and in the sql function you retrive the SET options of the "CurrSessionID". Theses sql function you call from C/AL -> ADO. But i'm not sure if there are exists some sys."Views" (maybe the sys.dm_tran_locks or you must join some others like the sys.dm_tran_XXX) in the master database which contains this infos.

Regards

mentat · 2008-09-12

thank you all for your help

bbrown · 2008-09-12

:oops:

garak · 2008-09-12

@mentat: do you have solved it?

mentat · 2008-09-12

garak wrote:

@mentat: do you have solved it?

not, yet. i let u know, don't worry

Detect isolation level (transaction type) in C/AL

Comments

Categories