LANSA and SQL Server - Configuration Options

Date:7 August 2009
Product/Version:Visual LANSA V11 SP5 and above **
Abstract:LANSA and SQL Server - Configuration Options
Submitted By:LANSA Technical Support
External(s):MS SQL Server 2005 and later versions

** Some of the features detailed below have been introduced to V11 SP5 via EPC. Refer to the EPC section of the LANSA web site for details.

Description:

The LANSA R&D division has conducted extensive research into the inner workings of SQL Server 2005 / 2008. This research was conducted to meet the requirements of our Customers and to ensure that LANSA utilizes the SQL Server engine in an optimal way. After investigating numerous Database options with Microsoft, three key areas of SQL Server concerning enterprise level I/O were defined:

  1. Local Savepoints
  2. Global Savepoints
  3. Backward Compatibility

We have found scenarios where SQL Server required parametric adjustments in order to process simple SQL I/O completely and successfully on a granular level (more details follow).

LANSA recommends keeping your I/O simple. Do not nest DML statements inside a SELECT loop. LANSA’s testing indicates that deeply nested I/O, such as calling functions or methods from within a SELECT loop and then performing more I/Os, may not work correctly in some combination with a particular set of data.

Global Savepoints vs. Local Savepoints

A Savepoint is an SQL Server-specific technique to release locks that SQL Server created and did not delete when an I/O failed due to a duplicate key, row not found or a lock timeout. This is SQL Server specific behaviour. The Savepoint technique is to restore to the state just prior to the I/O that failed.

When a file is not under commitment control, LANSA automatically performs a restore by rolling back the whole transaction and thus clearing all the locks. This is not a Savepoint but just a standard rollback.
When a file is under commitment control, if an I/O returns IO$STS=ER, then locks are cleared if a rollback is performed in RDML code. But prior I/Os may exist that the RDML programmer does not want to rollback. This is when savepoints are required.

Locally set a Savepoint by calling the SET_SESSION_VALUE BIF with DBSP=Y before the I/O and SET_SESSION_VALUE BIF with DBSP=N after the I/O. Note that setting DBSP=Y does not of itself set a savepoint. It just indicates to LANSA that when the next I/O occurs, a Savepoint should be set and potentially restored to that state if an error occurs. This will occur for every I/O until DBSP=N again.

Q&A 1

An alternative to setting savepoints on in RDML is Global Savepoints. This setting of LANSA sets a savepoint before each and every I/O and restores back to that savepoint when an I/O error occurs.

Q&A 2

So, in summary, savepoints will need to be locally set in your RDML code if you have coding constructs that do not rollback when an I/O error occurs. Alternatively, if modifying your code is not an option, you can set savepoints on globally.

Server-Side Cursors vs. Bind Session

Prior to LANSA Version 12.0, multiple connections were required by LANSA when accessing a SQL Server database because SQL Server did not allow more than one transaction per connection (by design). This behaviour was specific to SQL Server and was different from all other databases that LANSA supports. LANSA used one connection for modifying data (DML statements), which was called the ‘update connection’, and one or more for reading (SELECT statements). 

LANSA also had to allow the database changes made on the ‘update connection’ to be observable to the ‘read connection’ so that a single instance of LANSA did not block itself when updating data that was also being read. Our research and enquiries with Microsoft provided only one solution at the time of the research. We needed to use the stored procedure sp_bindsession to bind the update connection and read connections together. This works for most situations but does not support Savepoints.

When SQL Server 2005 was released, another option seemed to be available to allow a single connection to be used for all statements. This was the MARS technology - Multiple Active Result Sets. Significant LANSA resources were spent attempting to get this to work correctly and completely without success.

Since the stored procedure sp_bindsession was deprecated in SQL Server 2005, an alternative solution was required before sp_bindsession became unsupported in a version after SQL Server 2008.

For LANSA Version 12.0, we looked into the MARS technology again in consultation with Microsoft. Many of the issues raised by LANSA were escalated to Microsoft SQL Engineering. It turns out that MARS is only useful when a single statement is used for all transactions. Otherwise MARS had no effect.

In LANSA's case, we use one statement for each transaction. If there are multiple SELECTs active, we open another statement to handle the concurrent request. This is the standard way that other databases are usually used.

The SQL Server solution to allow multiple active statements per connection is to use server cursors. This link describes the differences between server-side and client-side cursors: http://msdn.microsoft.com/en-us/library/aa266531(VS.60).aspx

The LANSA SQL Server configuration options Local Savepoints and Global Savepoints both use server cursors. The Backward Compatibility configuration uses sp_bindsession.

Summary of SQL Server Configuration in LANSA

LANSA supports only three distinct SQL Server configurations:

  1. Local Savepoints, default
  2. Global Savepoints (set DBSP=Y)
  3. Backward compatibility with 11.4 and prior versions (set DBCT=0 and USE_SINGLE_CONNECTION=NO in x_dbmenv.dat.)

LANSA exposes some SQL Server options through settings in x_dbmenv.dat and through the X_RUN parameters. In order to use them you are strongly advised to study the SQL Server documentation extensively so that you fully understand the options before using them with LANSA.

In case of an issue (after making custom changes to SQL Server options), LANSA will require that you reproduce the issue with a default LANSA configuration as described above i.e. using either Local Savepoints, Global Savepoints or Backward Compatibility. All other custom changes to SQL Server settings and configurations are not certified or supported by LANSA.

Note: If you need to set Savepoints, then you must use either the Local or Global Savepoints sets of options as shipped apart from the DBCT and DBCC settings. Even then, the default ODBC settings for DBCT and DBCC must not be used together.

Parameter NameDescription
ODBANo longer required and therefore not supported
ODBITransaction isolation level.
Generally should not be set, which then uses the default isolation level of Read Committed. A new value of 6 is now available for Snapshot isolation level in SQL Server.
DBCTCursor Type
Defaults to Static cursor (3)
ODBC default = 0
SQL Server only
DBCCCursor Concurrency
Defaults to Read Only (1)
ODBC default = 1
SQL Server only
DBSPSet savepoint
Defaults to No
SQL Server only
DBMREnable MARS
Defaults to No
SQL Server only
USE_SINGLE_CONNECTIONUse a single connection for RDML IO
Defaults to Yes
Option is set in x_dbmenv.dat
Prior to 12.0 the default was No.

Note that LANSA sets SQL_COPT_SS_PRESERVE_CURSORS=ON when server cursors are used. It allows a single connection to have multiple active result sets. This is introduced in LANSA Version 12.0.

Detailed Information Regarding SQL Server Settings

The following is highly technical and SQL Server specific and thus is intended for skilled SQL Server database administrators and programmers.

Server-Side Cursors vs. MARS

LANSA requires that multiple cursors may be active at any one time, as discussed in the section above on Server-Side Cursors v Bind Session. There is a reference here on MARS: http://msdn.microsoft.com/en-us/library/ms345109(SQL.90).aspx#marsins_topic9

The critical difference between using server cursors and MARS is that server cursors allow multiple active statements per connection and MARS allows multiple active results per statement. MARS does not effect other statements on a connection.

With LANSA, we only ever have one active result set per statement. If we need a new result set, then we create a new statement. So MARS makes no difference to how LANSA operates.

LANSA would have to radically alter its database layer logic to use just one statement, but then setting savepoints would not work. The MARS documentation states "MARS disallows setting savepoints…if there is more than one active transaction". Microsoft have clarified that this refers to having a SELECT active whilst performing, say, an INSERT on the same statement handle. This is one particular scenario when we need to set a savepoint in order to work around the SQL Server behaviour of leaving locks on a row when the insert fails with a duplicate row error. If a savepoint was not used in this case, it could result in a deadlock due to the left locks. 

LANSA has confirmed with Microsoft that given the behaviour we require, LANSA must use server-side cursors. LANSA has also registered that locks are left when a transaction fails as a DB design defect with Microsoft. If this was not the case, then MARS would be an option and server-side cursors would not need to be used.

Isolation Level

The default isolation level in the SQL Server Native Client ODBC driver is Read Committed. The driver also supports other isolation levels. LANSA has performed some basic tests that indicate that Read Uncommitted, Snapshot, and Read Committed Snapshot may work correctly but we have done insufficient tests to warrant that they will work generally. LANSA cannot support these isolation states. If you experience an issue with one of these isolation levels, then you will need to reproduce it with the default isolation level before LANSA can support you.

  1. Read Uncommitted. We advise against this because the user may be presented with data that is never committed to the database. LANSA's crossed-update check does not detect this because it too reads the uncommitted data.
  2. Repeatable Read and Serializable may both cause an application to block itself. Thus we recommend against it.
  3. Snapshot (SI).
  4. Read Committed Snapshot (RCSI).

More information from Microsoft on RCSI vs. SI can be found at the end of this article in the section entitled "Read Committed Snapshot Isolation v Snapshot Isolation"

The database setting ALLOW_SNAPSHOT_ISOLATION ON is only required for Snapshot Isolation (SI). It is not required for Read Committed Snapshot Isolation (RCSI).

Also note that setting READ_COMMITTED_SNAPSHOT_ISOLATION ON only effects connections that use an isolation level of READ_COMMITTED. All other isolation levels ignore this setting.

Cursor Types

LANSA uses two parameters to control the cursor: Cursor Type and Cursor Concurrency. The default LANSA setting is to use a Static cursor that is Read Only. If you need to understand Cursors in SQL Server review the links provided below.

There are two X_RUN parameters that allow these options to be set: DBCT and DBCC. DBCT is for the Cursor Type and DBCC is for the Cursor Concurrency. They are passed directly through to the ODBC driver. Refer to the links below for the ODBC option and then the value of the option can be found in the file sqlext.h which is shipped by Microsoft. E.g. SQL_CURSOR_STATIC has the value 3.

When the Cursor Type or Cursor Concurrency is set to be different to the default settings, server cursors are used. The ODBC defaults are equivalent to DBCT=0 and DBCC=1. LANSA ships with DBCT=3 and DBCC=1, thus server cursors are used. These particular settings use a static read-only cursor.

LANSA cannot guarantee that Keyset Driven Read Only cursors and Dynamic Read Only cursors will work correctly. LANSA cannot support these cursor types. If you experience an issue with one of these cursor types then you will need to reproduce it with the default cursor type before LANSA can support you. 

Keyset Driven Concurrent Values is essentially an optimistic concurrency model which LANSA already implements using the crossed-update check. The Keyset Driven Concurrent Locks cursor type does not work with LANSA. 
Other cursor type combinations that may be possible and have not been mentioned here are not supported by LANSA.

When server cursors are used LANSA also sets SQL_COPT_SS_PRESERVE_CURSORS=ON. Microsoft has stated that setting the Transact SQL option CURSOR_CLOSE_ON_COMMIT to OFF can also activate the same behaviour. That is, the two methods are synonymous. What you read about one is true for the other. Here are some links for the latter method:

Memory Usage

SQL Server Locking

Here is an excellent resource for all locking issues. It also explains the lock types: http://www.nwsummit.com/tech/mssql.html

SQL Server Management Studio Activity Monitor will also display locks.

-- Microsoft Text Reproduced Verbatim --

Read Committed Snapshot Isolation vs. Snapshot Isolation

Here are some samples for you to understand RCSI and SI well:

  1. RCSI is a statement-level Snapshot isolation, which means any queries will see the most recent committed values as of the beginning of the statement. For example, let's look at the scenario as below - Assume two transactions are running in the AdventureWorks database, which has been enabled for RCSI, and that before either transaction starts running, the ListPrice value of Product 922 is 8.89.

A SELECT Running in RCSI

TimeTransaction 1Transaction 2
1BEGIN TRAN
UPDATE Production.Product
SET ListPrice = 10.00
WHERE ProductID = 922;
BEGIN TRAN
2 SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 8.89
3COMMIT TRAN 
4 SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 10.00
5 COMMIT TRAN

We should note that at Time = 2, the change made by Transaction 1 is still uncommitted, so the lock is still held on the row for ProductID = 922. However, Transaction 2 will not block on that lock in RCSI; it will have access to an old version of the row with a last committed ListPrice value of 8.89. After Transaction 1 has committed and released its lock, Transaction 2 will see the new value of the ListPrice.

  1. SI gives you a transactionally consistent view of the data. Any data read will be the most recent committed version as of the beginning of the transaction. (For RCSI, we get the most recent committed version as of the beginning of the statement.) A key point to keep in mind is that the transaction does not start at the BEGIN TRAN statement; for the purposes of SI, a transaction starts the first time the transactions accesses any data in the database.

    As an example of SI, let's look at a scenario similar to the one as above – it shows activities in a database with ALLOW_SNAPSHOT_ISOLATION set to ON. Assume two transactions are running in the AdventureWorks database and that before either transaction starts, the ListPrice value of Product 922 is 8.89.

A SELECT Running in a SI Transaction

TimeTransaction 1Transaction 2
1BEGIN TRAN 
2UPDATE Production.Product
SET ListPrice = 10.00
WHERE ProductID = 922;
SET TRANSACTION ISOLATION
LEVEL SNAPSHOT
3 BEGIN TRAN
4 SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 8.89
-- This is the beginning of
-- the transaction
5COMMIT TRAN 
6 SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 8.99
-- Return the committed
-- value as of the beginning
-- of the transaction
7 COMMIT TRAN
  SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 10.00

Event though Transaction 1 has committed, Transaction 2 continues to return the initial value it read of 8.99, until Transaction 2 completes. Only after Transaction 2 is done will the connection read a new value for ListPrice.

  1. The optimistic concurrency model of SI assumes (optimistically) that not many update conflicts will occur. You should not choose SI if you are expecting contention for updating the same data concurrently. SI works well to enable readers not to be blocked by writers, but simultaneous writers are still not allowed. In the default pessimistic model, the first writer will block all subsequent writers, but using SI, subsequent writers could actually receive error messages due to the conflict and the application would need to resubmit the original request. Note that these update conflicts will only occur with SI, not with RCSI.

    One crucial difference between the two optimistic concurrency levels is that SI can potentially result in update conflicts when a process sees the same data for the duration of its transaction and is not blocked simply because another process is changing the same data. The below sample illustrates two processes attempting to update the Quantity value of the same row in the ProductInventory table in the AdventureWorks database. Two clerks have each received shipments of ProductID 872 and are trying up update the inventory. The AdventureWorks database has ALLOW_SNAPSHOT_ISOLATION set to ON, and before either transaction starts, the Quantity value of Product 872 is 324.

An Update Conflict in SI

TimeTransaction 1Transaction 2
1 SET TRANSACTION ISOLATION
LEVEL SNAPSHOT
2 BEGIN TRAN
3 SELECT Quantity
FROM Production.ProductInventory
WHERE ProductID = 872;
-- SQL Server returns 324
-- This is the beginning of
-- the transaction
4BEGIN TRAN
UPDATE Production.ProductInvent
SET Quantity=Quantity + 200
WHERE ProductID = 872;
-- Quantity is now 524
 
5 UPDATE Production.ProductInventory
SET Quantity=Quantity + 300
WHERE ProductID = 872;
-- Process will block
6COMMIT TRAN 
7 Process will receive error 3960

The conflict happens because Transaction 2 started when the Quantity value was 324. When that value was updated by Transaction 1, the row version with 324 was saved in the version store. Transaction 2 will continue to read that row for the duration of the transaction. If both UPDATE operations were allowed to succeed, we would have a classic lost update situation. Transaction 1 added 200 to the quantity, and then transaction 2 would add 300 to the original value and save that. The 200 added by Transaction 1 would be completely lost. SQL Server will not allow that.

When Transaction 2 first tries to do the update, it doesn't get an error immediately and it is simply blocked. Transaction 1 has an exclusive lock on the row, so when Transaction 2 attempts to get an exclusive lock, it is blocked. If Transaction 1 had rolled back its transaction, Transaction 2 would have been able to complete its update. But because Transaction 1 committed, SQL Server detects a conflict and generates the following error:

Msg 3960, Level 16, State 2, Line 1

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'Production.ProductInventory' directly or indirectly in database 'AdventureWorks' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Conflicts are possible only with SI because that isolation level is transaction based, not statement based. If the above example were executed in a database using RCSI, the UPDATE statement executed by Transaction 2 would not use the old value of the data. It would be blocked when trying to read the current Quantity, and then when Transaction 1 finished, it would read the new updated Quantity as the current value and add 300 to that. Neither update would be lost.

If you choose to work in SI, you need to be aware that conflicts can happen. They can be minimized, but as with deadlocks, you cannot be sure that you will never have conflicts. Your application must be written to handle conflicts appropriately, and not assume that the UPDATE has succeeded. If conflicts occur occasionally, you might consider it part of the price to be paid for using SI, but if they occur too often, you might consider whether SI is really necessary, and if it is, you should determine whether the statement-based RCSI might give you the behavior you need without the cost of detecting and dealing with conflicts. 

  1. As we explained RCSI and SI above in details - RCSI is a statement-level Snapshot isolation while SI gives you a transactionally consistent view of the data. More space in tempdb for SI is to store those row versions with the longer life time.
Back to Top