isolation level

Eugene

is there a way to change isolation level from within C\AL code to something less strict than SERIALIZABLE ? Am i right to state that LOCKTABLE defaults to SERIALIZABLE ? Even if i do a simple INSERT or MODIFY it defaults to locking on SERIALIZABLE level ? Is there a way to change it all to READ_COMMITED ?

kine

There is no way how to change the isolation level in NAV.

There is only one property connected to that: TransactionType on report.

TransactionType
There are four basic transaction type options: Browse, Snapshot, UpdateNoLocks and Update. Each transaction type defines the behavior of a transaction in Navision and takes effect from the beginning of a transaction.

Additionally, there is a Report option that maps to one of the basic options. This enables a report to use the most concurrent read-only form of data access for the connected server. When you use Navision Database Server, it maps to Snapshot and when you use SQL Server, it maps to Browse.

The transaction behavior when using SQL Server is as follows:

Browse
This is a read-only transaction. Modifications cannot occur within the transaction. All read operations are performed with READ UNCOMMITTED locking. Therefore, no locks are placed and locks placed by other sessions are not honored. This means that it is possible to read uncommitted data.

Snapshot
This is a read-only transaction. Modifications cannot occur within the transaction. All read operations are performed with SERIALIZABLE locking. Therefore, share-locks are placed and are maintained until the end of the transaction. This guarantees a consistent snapshot of the data read within the transaction.

Microsoft's definition of "SERIALIZABLE" is as follows:

"A transaction isolation level that ensures that a database changes from one predictable state to another. If multiple concurrent transactions can be executed serially, and the results are the same, the transactions are considered serializable."

For further information about SERIALIZABLE locking, consult Microsoft's SQL Server documentation.

UpdateNoLocks
This is an update transaction. Modifications can occur within the transaction. The locking behavior for read operations on each table is the same as for the Browse transaction type until the table is either modified by a write operation or locked with the Record.LOCKTABLE function. From this point until the end of the transaction, all read operations are performed with both SERIALIZABLE and UPDLOCK locking.

This transaction type improves concurrency for all tables that users access within the transaction by delaying locking as much as possible. However, the disadvantage is that you must know when to lock the tables for the required transaction behavior.

This is the default transaction type if you have not specified a type with the CURRENTTRANSACTIONTYPE function.

Update
This is an update transaction. Modifications can occur within the transaction. The locking behavior for read operations on each table is the same as for the Snapshot transaction type until the table is either modified by any write operation or locked with the Record.LOCKTABLE function. From this point onwards, all read operations are performed with both SERIALIZABLE and UPDLOCK locking.

This transaction type provides full transaction isolation from the beginning of the transaction, regardless of the lock status of tables that users access within the transaction.

stryk

kine wrote:

There is no way how to change the isolation level in NAV.

Yes, there is: one could use the function CURRENTTRANSACTIONTYPE to return or set the transaction type.
But a LOCKTABLE (or FINDSET(TRUE)) will always set the Isolation Level to SERIALIZABLE.

Regards,
Jörg

kine

Ah, I forgot that one... never used... Each day something new... :whistle:

Eugene

thank you for your input but
unfortunately the TransactionType as well as CURRENTTRANSACTIONTYPE do only allow to set either SERIALIZABLE OR READ UNCOMMITED transaction isolation levels. I was hoping at least Snapshot would provide a SNAPSHOT isolation (for MS SQL 2005 or higher) level based on row versioning but it seams the snapshot is also setting READ UNCOMMITED until first update and SERIALIZABLE there after.

The only way i see to set isolation level to something different is through opening another session through ADO connections to the sql server directly using SET TRANSACTION ISOLATION LEVEL and doing direct SELECTs

Eugene

i was wrong about snapshot type in navision - it is read only transaction with SERIALIZABLE level of isolation. Anyway it is not what one could expect it to be starting MS SQL 2005 version