Microsoft® SQL Server™ offers both optimistic and pessimistic concurrency control. Pessimistic concurrency control is used by SQL Server by default. Optimistic concurrency control can be used with SQL Server by using cursors.
Optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely (but not impossible), and allows transactions to execute without locking any resources. Only when attempting to change data are resources checked to determine if any conflicts have occurred. If a conflict did occur, the application must read the data and attempt the change again (for example, if a second transaction updated a row the first transaction previously had read and wanted to update based on the previously read value that now does not exist).
Pessimistic concurrency control locks resources as they are required, for the duration of a transaction. Unless deadlocks occur, a transaction is assured of successful completion.
| Cursor Concurrency | Deadlocking |