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.

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:

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.

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.

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 PBODB126.INI file.
For example:

SAP Adaptive Server Enterprise

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

• 0—Read
  Uncommitted (dirty reads)

  Isolation level 0 prevents other transactions from changing
  data that an uncommitted transaction has already modified (through SQL statements such as UPDATE).

  Other transactions cannot modify the data until the transaction
  commits, but they can still read the uncommitted data (perform dirty
  reads). Isolation level 0 prohibits retrieval locks on tables or
  pages.

  Isolation level 0 is valid only for SAP System 10 or higher
  databases.

• 1—Read Committed

  (Default) Isolation level 1 prevents dirty reads by issuing
  shared locks on tables or pages.

  A dirty read occurs when one transaction
  modifies a table row and a second transaction reads that row before
  the first transaction commits the change. If the first transaction
  rolls back the change, the information read by the second transaction
  becomes invalid.

• 3—Serializable Transactions
  (HOLDLOCK behavior)

  Isolation level 3 prevents dirty reads, nonrepeatable reads,
  and phantoms for the duration of a transaction.

  A nonrepeatable read occurs when one
  transaction reads a row and then a second transaction modifies that
  row. If the second transaction commits the change, subsequent reads
  by the first transaction produce different results than the original
  read.

  A phantom occurs when one transaction reads a set of rows
  that satisfy a search condition, and then a second transaction modifies
  that data through a SQL INSERT, UPDATE,
  or DELETE statement. Subsequent reads by the first
  transaction using the same search conditions produce a different
  set of rows than the original read.

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.

• Development
  environment

  Select Read Committed from the Isolation Level drop-down list
  in the Database Profile Setup dialog box.

• Application

  Type the following in a script:

  PowerBuilder

  SQLCA.Lock="RC"

  1	SQLCA.Lock="RC"

• Development
  environment

  Select Serializable Transactions from the Isolation Level
  drop-down list in the Database Profile Setup dialog box.

• Application

  Type the following in a script:

  PowerBuilder

  SQLCA.Lock="3"

  1	SQLCA.Lock="3"

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.