Concurrent data access

Understanding concurrent data access and data consistency.

Data concurrency is the simultaneous access of the same data by many users. On the other hand, data consistency means that each user sees a consistent view of the database. Without adequate concurrency and consistency controls, data may be changed improperly, compromising data integrity. To write inter-operable applications, you must adapt the program logic to the behavior of the database server regarding concurrency and consistency management. This issue requires good knowledge of multiuser application programming, transactions, locking mechanisms, isolation levels and wait mode. If you are not familiar with these concepts, carefully read the documentation of each database server which covers this subject.

Processes accessing the database can change transaction parameters such as the isolation level. Existing programs might have to be adapted in order to work with this new behavior.

The following is the best configuration to get common behavior with all types of database engines:

  • The database must support transactions; this is usually the case.
  • Transactions must be as short as possible (under a second is fine, 3 or more seconds is a long transaction).
  • The isolation level should be set to COMMITTED READ or CURSOR STABILITY. IBM® Informix® IDS 11 has introduced the LAST COMMITTED option for the COMMITTED READ isolation level, which makes IDS behave like other database server using row-versioning, returning the most recently committed version of the row, rather than wait for a lock to be released. This option can also be turned on implicitly with the USELASTCOMMITTED configuration parameter, saving code changes.
  • The wait mode for locks must be WAIT or WAIT n (timeout). Wait mode can be adapted to wait for the longest transaction.

Remarks: With this configuration, the locking granularity does not have to be at the row level. To improve performance with IBM Informix, you can use the LOCK MODE PAGE locking level, which is the default. However, if the application requires row-level locking, you must use LOCK MODE ROW clause, or define the DEF_TABLES_LOCKMODEconfiguration parameter to ROW, in the onconfig file of the server.

Table 1. Concurrent data access differences in database engine brands
Database Server Type Concurrency topic
IBM DB2® LUW (UNIX™) Concurrency in IBM DB2
IBM Informix N/A
IBM Netezza® Concurrency in Netezza
Microsoft™ SQL Server Concurrency in SQL Server
Oracle® MySQL Concurrency in Oracle MySQL
Oracle Database Server Concurrency in Oracle DB
PostgreSQL Concurrency in PostgreSQL
SAP® ASE Concurrency in SAP ASE
SAP HANA® Concurrency in SAP HANA
SQLite Concurrency in SQLite