Concurrent data access

Understanding concurrent data access and data consistency.

Data concurrency is the simultaneous access of the same data by many users. Data consistency means that each user sees a consistent view of the data, while other users modify data through SQL transactions. The database systems are responsible for data concurrency and data consistency. Each database server has its own behavior and specifics regarding concurrency and consistency. Before writing inter-operable database applications, carefully read the documentation of all database engine you want to support.

The program code must be prepared to support the behavior of the database server regarding concurrency and consistency management. Programmers must have a good knowledge of multiuser application programming, transactions, locking mechanisms, isolation levels and wait mode.

Application processes accessing the database can change transaction parameters such as the isolation level and lock wait mode. Existing programs might have to be adapted in order to work with this feature, as it changes the behavior of the SQL statement execution.

Basically, when a process modifies data during a transaction, other (writing) processes will have to wait, until the first process terminates its transaction. The longer the other processes have to wait, the worse the user experience becomes. Keep this in mind when programming with SQL.