| SQL adaptation guide for SQL SERVER 2005, 2008, 2012, 2014, 2016 / Database concepts | |
Data consistency involves readers which want to access data currently modified by writers and concurrency data access involves several writers accessing the same data for modification. Locking granularity defines the amount of data concerned when a lock is set (row, page, table, ...).
Informix uses a locking mechanism to manage data consistency and concurrency. When a process modifies data with UPDATE, INSERT or DELETE, an exclusive lock is set on the affected rows. The lock is held until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks according to the isolation level . In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode .
Control:
Defaults:
As in Informix, SQL SERVER uses locks to manage data consistency and concurrency. The database manager sets exclusive locks on the modified rows and shared locks or update locks when data is read, according to the isolation level . The locks are held until the end of the transaction. When multiple processes want to access the same data, the latest processes must wait until the first finishes its transaction or the lock timeout occurred. The locking strategy of SQL SERVER is row locking with possible promotion to page or table locking. SQL SERVER dynamically determines the appropriate level at which to place locks for each Transact-SQL statement.
Starting with SQL Server 2005, you can enhance concurrency by turning on snapshot isolation level, to make SQL Server use a copy of the row when it is changed by a transaction. To turn this feature on, you must set the database property ALLOW_SNAPSHOT_ISOLATION ON. Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level (otherwise, snapshot isolation must be specified by every SQL Session).
Control:
Defaults:
The SET ISOLATION TO ... in programs is converted to SET TRANSACTION ISOLATION LEVEL ...for SQL Server. The next table shows the isolation level mappings done by the database driver:
| SET ISOLATION instruction in program | Native SQL command |
|---|---|
| SET ISOLATION TO DIRTY READ | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED |
SET ISOLATION TO COMMITTED READ [READ COMMITTED] [RETAIN UPDATE LOCKS] |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
| SET ISOLATION TO CURSOR STABILITY | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| SET ISOLATION TO REPEATABLE READ | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE |
For portability, it is recommended that you work with Informix in the read committed isolation level, to make processes wait for each other (lock mode wait) and to create tables with the "lock mode row" option.
When using SET LOCK MODE ... in the programs, it will be converted to a SET LOCK_TIMEOUT instruction for SQL SERVER:
| SET LOCK MODE instruction in program | Native SQL command |
|---|---|
| SET LOCK MODE TO WAIT | SET LOCK_TIMEOUT -1 (wait forever) |
| SET LOCK MODE TO WAIT seconds | SET LOCK_TIMEOUT seconds * 1000 (wait N milliseconds) |
| SET LOCK MODE TO NOT WAIT | SET LOCK_TIMEOUT 0 (do not wait) |
See Informix and SQL SERVER documentation for more details about data consistency, concurrency and locking mechanisms.