Concurrency Control

In a client-server environment, there can be multiple client programs simultaneously accessing the same data in the database server. When concurrency control is enabled, any row updates or deletions in an SQL table will succeed only if the rows concerned have not changed in the database since the last time they were fetched and stored in the local memory cache. In other words, concurrency control obliges the SQL table to protect the work carried out through it from any changes made by other database users.

This is achieved by adding extra conditions in the SQL statements that are generated when a row is updated or deleted.

For example, assume that the SQL SELECT statement of an IliSQLTable object is the following and that ID is the primary key of the EMP table:

SELECT ID, NAME

FROM EMP

If the name of an employee whose ID is 6 is changed from “Smith” to “Jones”, the resulting SQL UDPATE statements will be as follows:

• Without concurrency control

UPDATE EMP SET NAME = ‘Jones’ WHERE ID = 6

• With concurrency control enabled

UPDATE EMP SET NAME = ‘Jones’ WHERE ID = 6 AND NAME = ‘Smith’

The latter statement will fail if the name of the employee has been changed by another user.

Note The technique used to deal with concurrency control is referred to as optimistic concurrency control. It does not explicitly lock rows at read time. For more information on how rows can be locked at read time using a technique known as pessimistic concurrency control, see Auto-Row Locking Mode .