In a single-user database, the user can modify data in the database without concern for other users modifying the same data at the same time. However, in a multi-user database, the statements within multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful and consistent results. Therefore, control of data concurrency and data consistency is vital in a multi-user database.
There are three common techniques for managing what happens when users try to modify the same record at the same time: pessimistic, optimistic, and Last-in wins.
Last-in wins: It is the default option and accepts changes from all users regardless of working(Editing) on the same record. Now it's clear that there is Concurrency violation.
Pessimistic: It supports Single user database. No other user can't fetch the data until one's changes are completed . Of course This technique doesn't violate the concurrency But the major Problems is all Users have to wait until the lock has been released from the current user who editing particular records. Suppose Current user forgot to unlock, all users have to wait until the release. It leads to wastage of Database resources as well as end user's valuable time.
Optimistic: It is best practice for Multi user database. Multiple Users can fetch same data simultaneously, but not supporting editing for the users who are working on wrong data. i.e. the fetched data is differing with Original data in the database. Developers Programmatically give instructions to users such as "This record is updated by another User… Please try again".
Optimistic Data Concurrency
These are the following techniques to implement Optimistic Data Concurrency.
- Using Where Clause
By this technique each column value is compared with the Original DB value before updating. It is best suited for small tables (less number of columns). In case of a table with 100 columns, we have to compare 100 columns even for a single column update. It leads to unnecessary usage of database resources. By thus Performance decreases.
2. Maintain a Timestamp column
Timestamp column in Sqlserver doesn't store any date or time. It simply a stores binary data with a size of one Byte. It is Unique and only one for a table. Timestamp column value Update automatically while insert/Update. Programmers no need to write a code to update the timestamp column for each insert/Update. The only thing they will have to do is fetch the Timestamp column value along with Original data. And again fetch the Timestamp value of a record before update. If both timestamp values are matched, it means that no one Update the record so far. So make it updatable. Otherwise Give an instruction that "Current record is Updated by someone. Please Try again"
3. Modified Columns
There is no need of a Timestamp column field where end users split on Updations . For example User A can Edit only First 3 records in a table and User B can edit only Last 3 records. In that case they can implement transactions.
Optimistic Data Concurrency plays vital role especially for multi user database. I hope this article will helps to those who start DB applications. I didn't give any explanation to Sample application program why because it is easy to understand.