lock isolation

Set the isolation level for the database transactions of the connector.

USYS$Mnemonic_PARAMS = lock isolation:Level

USYS$Mnemonic_PARAMS = iso:Level

Note:  Valid values for Level depend on the DBMS connector.

Valid Values

Level is one of the following:

Valid Values per Connector
Long Value Short Value Meaning Applies To
repeatable read rr Highest level of isolation; has the most overhead and is the safest. DB2, MSS, MQL, PGS
read stability rs Equivalent to repeatable read. DB2, PGS
serializable sl Equivalent to repeatable read. DB2, MSS, MQL, PGS
read committed rc Medium level of isolation.

Default level for all connectors except DB2, which is cursor stability.

 MSS, MQL, PGS
cursor stability cs Equivalent to read committed. Default for DB2. DB2
read uncommitted ru Lowest level of isolation; has the least overhead, but is considered unsafe regarding data consistency. MSS, MQL, PGS
uncommitted read ur Equivalent to read uncommitted. DB2
statement level snapshot ss Usable only if you also set the read_committed_snapshot in the database to on. To do so, execute the following script:
alter database MYDATABASE
set read_committed_snapshot on
with <termination>
 MSS
transaction level snapshot ts Usable only if you set the allow_snapshot_isolation in the database to on. To do so, execute the following script: 
alter database MYDATABASE
set allow_snapshot_isolation on
 MSS

Caution: These names of these levels are defined by the SQL-92 standard. However, the names employed by different databases do not always correspond to these names. For example, DB2 defines repeatable read as serializable and read stability as repeatable read. Consult your database documentation for the terminology used by your database.

Use

Applies to Mnemonic Connector Syntax
DB2 DB2 USYS$DB2_PARAMS
Microsoft SQL Server connector MSS USYS$MSS_PARAMS
MySQL connector MQL USYS$MQL_PARAMS
PostgreSQL connector PGS USYS$PGS_PARAM

Description

A higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another.

Related Topics