Concurrency could be defined as an ability of multiple sessions to access a shared data at the same time. Concurrency comes in to picture when a request is trying to read data and the process prevents the other requests to change the same data or vice versa.

The RLV discussed above allows concurrent access automatically with no additional application control to avail this feature. Now any relational database could support multiple and simultaneous connections to the database. The job of handling concurrencies between the requests is usually handled by the server. SQL Server internally takes care of the blocking issues between two or more processes. But sometimes it may be necessary to take over some part of the control over the concurrent access to maintain the balance between data consistency and concurrency.

There are two kinds of concurrency control: Optimistic concurrency control and pessimistic concurrent control. SQL Server has a pessimistic concurrency model by default. So by default, other transactions could not read the data until the current session commits, which in this case is a writer block. Locking could prove a good choice for many of today’s database systems, but it may also introduce blocking issues. If the results are based on only the committed data then the only option left is to wait until changes are committed.

To put it in a straight forward manner, a pessimistic concurrency control the system is pessimistic. It assumes that a conflict will arise when a read operation is requested over the data modification of other users. So in this case locks are imposed to ensure that the access to the data is blocked which is being used by other session.

But in the case of optimistic concurrency, it works with an assumption that any request could modify data which is being currently read by another request. This is where the row level versioning is being used which checks the state before accessing the modified data.