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
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:
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 |
|
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 MSOLEDBSQL Microsoft OLE DB Driver for SQL Server |
RU RC RR SS TS TC |
Read Uncommitted Read Committed (default) Repeatable Read Snapshot Serializable Transactions Chaos |
|
SAP Adaptive Server Enterprise |
0 1 3 |
Read Uncommitted Read Committed (default) Serializable Transactions |
|
SAP 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 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:
[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:
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, 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:
SQLCA.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:
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.
