Lock

Description

For those DBMSs and database interfaces that support the use of lock values and isolation levels, the Lock preference sets the isolation level to use when connecting to the database.

In multiuser databases, transactions initiated by different users can overlap. If these transactions access common data in the database, they can overwrite each other or collide.

To prevent concurrent transactions from interfering with each other and compromising the integrity of your database, certain DBMSs allow you to set the isolation level when you connect to the database. Isolation levels are defined by your DBMS, and specify the degree to which operations in one transaction are visible to operations in a concurrent transaction. Isolation levels determine how your DBMS isolates or locks data from other processes while it is being accessed.

PowerBuilder uses the Lock preference to allow you to set various database lock options. Each lock value corresponds to an isolation level defined by your DBMS.

NoteWhen to specify the Lock value You must set the Lock value before you connect to the database. The Lock value takes effect only when the database connection occurs. Changes to the Lock value after the connection occurs have no effect on the current connection.

Applies to

Context

In an application

For those DBMSs and database interfaces that support it, you can set the Lock value in code as a property of the Transaction object. The following syntax assumes you are using the default Transaction object, SQLCA, but you can also use a user-defined Transaction object:

SQLCA.Lock =’value’

where value is the lock value you want to set.

Lock values

The following table lists the lock values and corresponding isolation levels for each database interface that supports locking. You set the lock value in code, and the isolation level in a database profile.

For more about the isolation levels that your DBMS supports, see your DBMS documentation.

Database interface

Lock values

Isolation levels

IN9 and I10 Informix (for OnLine databases only)

Dirty Read

Committed Read

Cursor Stability

Repeatable Read

Dirty Read

Committed Read

Cursor Stability

Repeatable Read

JDB JDBC

RU

RC

RR

TS

TN

Read Uncommitted

Read Committed

Repeatable Read

Serializable Transactions

Transaction None

ODBC

RU

RC

RR

TS

TV

Read Uncommitted

Read Committed

Repeatable Read

Serializable Transactions

Transaction Versioning

OLE DB

RU

RC

RR

TS

TC

Read Uncommitted

Read Committed

Repeatable Read

Serializable Transactions (default)

Chaos

SNC SQL Native Client

RU

RC

RR

SS

TS

TC

Read Uncommitted

Read Committed (default)

Repeatable Read

Snapshot

Serializable Transactions

Chaos

Sybase Adaptive Server Enterprise

0

1

3

Read Uncommitted

Read Committed (default)

Serializable Transactions

Sybase DirectConnect

0

1

2

3

Read Uncommitted

Read Committed (default)

Repeatable Read

Serializable Transactions

In the development environment

Select the isolation level you want from the Isolation Level drop-down list on the Connection tab in the Database Profile Setup dialog box.

For instructions, see “Setting Additional Connection Parameters” in Connecting to Your Database.

Default

The default lock value depends on how your database is configured. For information, see your DBMS documentation.

Usage

ODBC The TV (Transaction Versioning) setting does not apply to SQL Anywhere databases.

OLE DB The default value for Lock in the discontinued MSS native interface and the SNC interface for Microsoft SQL Server 2005 is Read Committed, but for OLE DB the default is Serializable Transactions. If you want to connect to SQL Server 2000 using OLE DB, you can override the default value by specifying a value for Lock in the PBODB125.INI file. For example:

[Microsoft SQL Server]
...
LOCK='RC'
...

The value in the PBODB125.INI file is used if you do not change the default in the database profile or set the Lock parameter of the Transaction object in code.

Sybase Adaptive Server Enterprise Sybase Adaptive Server Enterprise supports the following lock values, which correspond to SQL Server isolation levels:

Dynamically controlling the isolation level PowerBuilder makes a second connection to implement either of the following while connected to a Sybase Adaptive Server Enterprise database:

The lock value you set before making the first Adaptive Server Enterprise connection is automatically inherited by the second connection, and cannot be changed for the second connection.

However, you can dynamically control the isolation level for the first (original) Adaptive Server Enterprise connection in an application by coding the following PowerScript embedded SQL statement, where n is 0, 1, or 3 for the isolation level you want to set for the first connection:

EXECUTE IMMEDIATE "set transaction isolation level n"

For example, the following PowerScript embedded SQL code specifies isolation level 0 (dirty read behavior) for the second connection, and isolation level 1 (read committed behavior) for the first connection:

// Isolation level inherited by second connection

SQLCA.Lock="0"

CONNECT USING SQLCA;

// Override lock value 0 for first connection only

EXECUTE IMMEDIATE "set transaction isolation level 1";

Use in three-tier applications If an ASE connection on an application server, such as EAServer, is used by a component with a specified isolation level and cached by the server, it is released back into the connection pool with the isolation level set by the component. If that connection is then used by another component that has no specified isolation level, the isolation level may not be the default level expected by the component (1). This could result in the occurrence of deadlocks. To avoid this, always set the SQLCA.Lock property explicitly in application server components.

Examples

Example 1

Example 1 To set the Lock value to RC (Read Committed) for a SQL Anywhere database:

Example 2

Example 2 To set the Lock value to 3 (Serializable Transactions) for a Sybase Adaptive Server Enterprise database:

Using the examples in code

If you specify Isolation Level in your database profile, the syntax displays on the Preview tab in the Database Profile Setup dialog box. You can copy the syntax from the Preview tab into your code.