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.
DataWindow Designer 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.
ASE, SYC Sybase Adaptive Server Enterprise
DIR Sybase DirectConnect
IN9 Informix (OnLine databases)
ODBC (if driver and back-end DBMS support this feature)
OLE DB
SNC SQL Native Client for Microsoft SQL Server
For those DBMSs and database interfaces that support it, you can set the Lock value in code as a property of the Transaction object:
SQLCA.Lock =’value’
where value is the lock value you want to set.
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 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 |
RU RC RR SS TS TC |
Read Uncommitted Read Committed Repeatable Read Snapshot Serializable Transactions (default) 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 |
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.
The default lock value depends on how your database is configured. For information, see your DBMS documentation.
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 PBODB110.INI file. For example:
[Microsoft SQL Server] ... LOCK='RC' ...
The value in the PBODB110.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:
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 Sybase 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.
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 a Sybase 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"
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.
