Lock
database preference
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.
When 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
ASE and SYC SAP Adaptive Server Enterprise
DIR SAP DirectConnect
I10 Informix
IN9 Informix
JDB JDBC
ODBC (if driver and back-end DBMS support this feature)
OLE DB
SNC SQL Native Client for Microsoft SQL Server
MSOLEDBSQL Microsoft OLE DB Driver for SQL Server
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:
|
1 |
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 |
Dirty Read Committed Cursor Stability Repeatable |
Dirty Read Committed Cursor Stability Repeatable |
|
JDB JDBC |
RU RC RR TS TN |
Read Uncommitted Read Repeatable Read Serializable Transaction None |
|
ODBC |
RU RC RR TS TV |
Read Uncommitted Read Repeatable Read Serializable Transaction Versioning |
|
OLE DB |
RU RC RR TS TC |
Read Uncommitted Read Repeatable Read Serializable Chaos |
|
SNC SQL Native Client MSOLEDBSQL |
RU RC RR SS TS TC |
Read Uncommitted Read Committed Repeatable Snapshot Serializable Chaos |
|
SAP Adaptive Server Enterprise |
0 1 3 |
Read Uncommitted Read Committed Serializable Transactions |
|
SAP DirectConnect |
0 1 2 3 |
Read Uncommitted Read Committed Repeatable Read Serializable |
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 the section called “Setting Additional Connection Parameters” in Connecting to Your Database.
Default value
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 PBODB.ini file.
For example:
|
1 2 3 4 |
[Microsoft SQL Server] ... LOCK='RC' ... |
The value in the PBODB.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.
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.
Dynamically controlling the isolation
level
PowerBuilder makes a second connection to implement either of the
following while connected to an SAP Adaptive Server Enterprise
database:
-
The Retrieve.AsNeeded property to specify that a DataWindow
should retrieve only as many rows as needed from the database -
A SELECTBLOB embedded SQL statement to select a single blob
column in a specified table row
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:
|
1 |
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:
|
1 2 3 4 5 |
// 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, 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
To set the Lock value to RC (Read Committed) for a SQL Anywhere
database:
-
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:
1SQLCA.Lock="RC"
Example 2
To set the Lock value to 3 (Serializable Transactions) for an SAP
Adaptive Server Enterprise database:
-
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:
1SQLCA.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.