Blocked connections with your SQL Server implementation

Date:19 November 2001
Updated:10 February 2006
Product/Release:Visual LANSA
Abstract:Blocked connections with your SQL Server implementation
Submitted By:LANSA Technical Support
External(s):Microsoft SQL Server 7.0 and 2000

Update:

Form V11.3 EPC771, database Connections for SQL Server Database are now bound, removing the need for ODBI=1 being specified where an application will attempt to read uncommitted data from the same transaction set.

Description:

Before you go any further with a SQL Server implementation, it is highly recommended that you have attended an SQL Server Administrator's course and that you have read the SQL Server documentation before you perform any work with SQL Server. SQL Server is a highly sophisticated DBMS with many options which if not properly understood can at the best waste many hours of your time and at the worst lose your data. Please ensure you fully understand Security, Permissions, System Stored Procedures and Transact-SQL before progressing with your implementation.

LANSA Support will presume that you have access to the SQL Server 2000 Administrator's Pocket Consultant when answering any questions you may have.

We highly recommend that the X_RUN option ODBI=1 be used with SQL Server so that blocked connections do not occur. Blocked connections can occur with SQL Server because only one SQL transaction can occur on a connection at a time, unlike all other LANSA supported databases which support an unlimited number of transactions per connection.

Setting ODBI=1 allows Dirty Reads to occur. This means that data can be read by user A when it is has been updated by user B but the update has not yet been committed. User A's data will be the state of the data BEFORE the update occurred. If user A then updates the data (after user B has committed the update) LANSA will reject the change saying that the cross update check failed. User A will need to re-read the data and make the changes again. If ODBI=0, user A would be locked from reading the data until user B had committed the data. This is not usually a problem because typically user B has momentarily dirtied the data and commits it very quickly anyway and so user A will have a slightly longer access time to get the data as it waits for the lock to be released.

The critical situation is the special case of a user locking themselves out from updating another record, but in the same page. This happens far more frequently than the general case of 'some other' user and is fatal because it is impossible to free yourself. It is effectively a dead lock, though technically it is caused by blocked connections. Thus we recommend the use of ODBI=1.