Will a read only query in Excel lock the records in MS SQL Server 2005 database?

Question

Hello,

We have an ERP system from Lawson running on Windows Server 2003 and the backend database is MS SQL Server 2005. Occasionally we will experience the program dump in ERP system due to the database lock on records cannot be obtained.

The technical support from our ERP software supplier said this is caused by the outside database call which locked the records in the database. Specificly they mentioned the query from Excel - "If you use Microsoft Office, we often see MS Excel being used to pull data directly from M3. By default Office requests a full, exclusive lock on the tables and cause this kind of problem.".

We do have a lot of queries running from Excel, but all of them are with 'Select' statement only. We have no query in Excel will update the database, all of our query in Excel are read only from the database.

Is the statement of "By default Office requests a full, exclusive lock on the tables" ture? Expecailly on a read only query? By common sense a read only query should not lock the backend table.

In case of in fact Excel does lock the records for a read only query, if there any parameters / register key entries we can change so that it will not lock the table for read only query.

Thank you for your help.

Answer 1

Is the statement of "By default Office requests a full, exclusive lock on the tables" ture? Expecailly on a read only query? By common sense a read only query should not lock the backend table.

Yes this statement is correct.  Select statements can cause table locks if they use wrong isolation levels (additional MSDN link).

In case of in fact Excel does lock the records for a read only query, if there any parameters / register key entries we can change so that it will not lock the table for read only query.

Not sure about this one.  Hopefully someone else will know the answer.

You may be able to configure the database to use a Row Versioning-based Isolation Level

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 2

Hi Jeff,

Thanks for your links on the Isolation Levels. Do you know what isolation level was used by Excel query with 'Select' statement only? Will the query with 'Select' statement starts a transation on the database side?

I have verified that the Row Versioning-Based Isolation Levels in set to OFF on the database for our ERP system. If we turn on the Row Versioning-Based Isolation Levels on the database, will that cause any potential data integrety issue?

Thanks

Answer 3

Hi Justin Wang,

As Mr.Wharty indicates, the transaction isolation level controls the locking versioning behavior.

For READ UNCOMMITED, shared locks are not acquired. Therefore, transactions are not blocked by exclusive locks from reading rows that have been modified but not committed by other transactions, so dirty read is possible.

For READ COMMITED (default isolation level), shared locks are acquired and released immediately. Dirty read is prevented.

For REPEATABLE READ, SNAPSHOT and SERIALIZABLE, shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes in only.

The technical support from our ERP software supplier said this is caused by the outside database call which locked the records in the database. Specificly they mentioned the query from Excel - "If you use Microsoft Office, we often see MS Excel being used to pull data directly from M3. By default Office requests a full, exclusive lock on the tables and cause this kind of problem.".

If the queries from Excel are just SELECT statements, I don’t think the queries will acquire any exclusive locks to block other transactions.

To troubleshoot blocking issues, you can refer to the following KB article:

http://support.microsoft.com/kb/224453

You may also post the question in an Office forum for more help.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 4

Hi Jian,

Thanks you for your respond and I have posted the same question on the 'Excel for Developer Forums'.

I understand the shared lock and isolation level in general, but I don't know the details on how Excel uses the isolation level and how Excel does the transaction control when running query to MS SQL Server tables.

Below is the respond I got from an expert in the development group of Lawson in regard to this issue:

Database locking is tricky, especially in multi user environments…

For M3 BE we always use isolation level “read uncommitted” when talking to the database. This is to avoid unnecessary locks to be acquired on
tables just opened to read. Most tools developed for a single user scenario (Excel and others) do NOT by default use this isolation level! Instead they acquire full, exclusive locks on all tables opened just in case…

Also one very common misconception is that “if I only do select statements it will be no lock” but that is wrong! The lock/no-lock is determined by the isolation level, not by the sql statement.

This might causes us (and others) some serious troubles in some cases, since our transaction engine is not able to resolve lock situations with external connections involved.

If there is only internal transactions involved we have the appropriate mechanisms in place to reverse transactions back to the point where the lock is resolved so we can redo the transactions that got stuck. But if there is an external lock held somewhere in that stack of locks we can’t get hold of that transaction and if we could we wouldn’t know what to do with it so the only thing is to wait for the time-out!

Also, to further complicate things,Excel (and other single user environment tools) have no real god way of defining the boundaries of a transaction. In many cases an exclusive lock is maintained from first opening of the table throughout the whole lifetime of that session i.e. until Excel is closed…

That’s why applying the appropriate isolation level becomes of outmost importance!

Alternatively, if the amount of queries is extensive one could always consider setting up a replica of the database/tables that gets updated through transaction Log Shipping or any other solution and by that offload this work from the production database. In scenarios where failover capabilities for the database is in place this type of work is typically run on the passive side.

Then comes the issues with external applications that are to update the database.

Recommendation in this case is to, if possible:

• Make sure to keep transactions as short and atomic as possible!
• Acquire locks on tables in a way that minimizes the time the lock need to be held
• Apply some means of lock principles, so deadlock situation are avoided (alphabetical, numerical, whatever order that make sense – important isto use same method everywhere)
• Make sure to fully release locks when not needed any longer

Doing this will in most cases make it work, but there are no full warranties…

Finally – where is the isolation level set? Normally you find it somewhere in the definition of the connection (i.e. ODBC setup) but it’s hard to be specific here since it depends on what driver and on what operating system that is in use.

There are some good guidelines in the above respond, but I have no knowledge in regard to the two stetements being highlighted in yellow:

The first statement is - 'Most tools developed for a single user scenario (Excel and others) do NOT by default use this isolation level! Instead they acquire full, exclusive locks on all tables opened just in case…'. My knowledge is by default SQL Server and the ODBC driver for SQL Server set the isolation level to 'READ COMMITTED', in which case a shared lock instead a full, exclusive locks will be acquired for the read operations. I don't know if this statement is true or not.

And the second statement is - 'Excel (and other single user environment tools) have no real god way of defining the boundaries of a transaction. In many cases an exclusive lock is maintained from first opening of the table throughout the whole lifetime of that session i.e. until Excel is closed…'.  I have no knowledge on how Excel manages the boundaries of a transaction. If this statement is ture, then it will be extremly bad.

Finally, I would like to know how to change the default isolation level for the ODBC driver for SQL Server. I know I can add 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED' at the begining of my query, but this will need to be done for every single query. I tried to add 'SQL_TXN_ISOLATION=SQL_TXN_READ_UNCOMMITED' to the connection string, but Excel removed it automatically. I cannot find anywhere in the OBDC settings to specify the default isolation level.

If I use SQLOLEDB instead of ODBC to make the connection, then Excel will accept the 'Extended Properties = "SQL_TXN_ISOLATION=SQL_TXN_READ_UNCOMMITED";' as part of the connection string.

The queries developed by myself will normally use OLEDB instead of ODBC, but the queries developed by our end users will only use ODBC to make the connection.

Thanks

Justin

Answer 5

Hello,

I am not sure that this link could help you.I provide it only for information and as a complement to the excellent posts of Jeff and Jian :

http://msdn.microsoft.com/en-us/library/ms713605(v=vs.85).aspx 

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

First of all if you are using any transaction with insert update query then select query will not work, you have to use nolock keyword but will give inconsistent data.