INF: Q+E and MS Query Can Cause Long-Term Page Lock

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

The query tools Microsoft Query and Q+E (which ship or have shipped with Microsoft Excel) can cause a long-term shared page lock on SQL Server when browsing data. Under some conditions, this can block updates to the table. This is because data modification queries can require an exclusive page or table lock, which can be blocked by the outstanding shared page lock from Q+E or Microsoft Query.

MORE INFORMATION

You can identify whether this has happened by running the SQL Server query sp_lock. Identify which SPID number corresponds to the client computer running Q+E or MS Query, and then note whether a sh_page lock exists.

With MS Query, you may want to use the >| button to go to the last row in the table, at which point MS Query will release the page lock. The data will then be locally available for browsing in MS Query. Similarly with Q+E, you may want to drag the scroll bar to the last record, which will cause all records to be fetched at which point the page lock will be released.

For more information on application design and concurrency control see Appendix E, "Maximizing Database Consistency and Concurrency" in the SQL Server for Windows NT Programmer's Reference for C.