Updating and concurrency
Ventura is optimized to process large numbers of short lived request-response transactions.
Concurrency can be defined as the ability for multiple processes to access or change shared data at the same time. The greater the number of concurrent user processes that can be active without interfering with each other, the greater the concurrency of the database system.
Ventura SQL uses the ADO.NET DbDataReader class, as a forward-only cursor, to retrieve resultset rows.
Executing an SQL script that produces one or more resultsets opens the datareader. As long as the datareader is open the database engine locks rows (and maybe also tables). For that reason resulting rows are read as fast as possible and data is copied into RAM memory. The datareader is then closed and locks are released.
This approach allows maximum concurrency.
INSERT, UPDATE and DELETE statements are executed within a database transaction. The statements are executed as fast as possible and the transaction time is kept as short as possible.
'Last in wins' concurrency management
Ventura SQL uses the so called 'client-wins' or 'last in wins' approach to concurrency management. The last user to save their changes will cause an overwrite of all previous changes.
When modified and marked-for-deletion records are send to the database via Recordset.SaveChanges or Transactional.SaveChanges, the resulting UPDATE and DELETE statements locate a row by primary key.
If you have a situation where you need to detect if another user changed a table row after it was retrieved, you would have to create a recordset (without resultsets) with specific SQL script for that situation where all columns and their original values are listed after the WHERE statement.
UPDATE <data values> WHERE <prikey values> AND <all original data values>
Another, more compact, solution is the RowVersion approach.
Here you update a timestamp column each time the table row is modified.
UPDATE <data>, <new timestamp> WHERE <prikey> AND <timestamp>
If another user has edited the row, the WHERE clause won’t match, and no rows will be updated.