Optimistic and Pessimistic Concurrency
Microsoft® SQL Server™ 2000 offers both optimistic and pessimistic concurrency control. Optimistic concurrency control uses cursors. Pessimistic concurrency control is the default for SQL Server.
Optimistic Concurrency
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 occurs, the application must read the data and attempt the change again.
Pessimistic Concurrency
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.