SQL Report locks NAV Table

gob_Behnke

Hi,

actually I have a customer that has problem with a sql report (SQL Server 2008) that locks a table in MS Dynamics NAV 2009.

Although the report is quite complex, it only reads records from the database, there is no write process (tested it with SQL Profiler),
thus I don't know why the table gets locked.

Is there a way to solve this issue.


Any help is greatly appreciated

Kind regards

bbrown

The default isolation level is "Read Committed" which will issue shared locks.

bbrown

I read this to understand that you are talking about a report using a SQL tool external to NAV. Correct?

gob_Behnke

Thanks for you fast answer.

Yes, that's correct.

The tool I use is Microsoft SQL Server Reporting Services.
It's part of Microsoft SQL Server, so I think it is a quite common tool.

How can I change the default isolation level via SQL?

bbrown

You might consider turning on Read Committed Snapshot Isolation for the database. But be sure you have TempDB on a different disk (with enough space) from the NAV databasea and log files. Also have 1 tempDB file for each processor core (up to 8 ).

gob_Behnke

Isn't there a way to change the isolation level?

I can't change anything about the target database.

bbrown

You can change the level for a given session using the "SET TRANSACTION ISOLATION LEVEL" T-SQL command. But I suggest you read the definition of each level before deciding on one to use. Each has its pros and cons.

Rsther then doing this, I'd suggest taking a look at redesigning the report to reduce the lock granularity and durations

gob_Behnke

Thanks, I guess I can't change the report itself (without great effort), but
the "SET TRANSACTION ISOLATION LEVEL" - Command solved the issue.

As far as I understood the command, the only disadvantage are dirty reads,
but this is no problem.

Thanks a lot.