1

I would like to know which concurrency control is more efficient, pessimistic or optimistic concurrency control? Is there a special reason why SQL Server 2005 uses pessimistic concurrency control as default? Is it for performance related reasons?

Thanks in anticipation

Sreedhar Danturthi
  • 7,119
  • 19
  • 68
  • 111

2 Answers2

2

This depends entirely on what concurrency needs your application has. If you are developing an OLTP app, pessiimistic would probably be best... If you are developing a single-user database, optimistic is fine.

therealmitchconnors
  • 2,732
  • 1
  • 18
  • 36
  • can you answer my 2nd part of question i.e., Is there a special reason why SQL Server 2005 uses pessimistic concurrency control as default? Is it for performance related reasons?Thanks. – Sreedhar Danturthi Jun 23 '11 at 19:21
  • Pessimistic is "safer" in that you will never have concurrency faults go unnoticed. It is also slower. – therealmitchconnors Jun 23 '11 at 19:26
1

I am not sure what do you mean by "SQL Server 2005 uses pessimistic concurrency control as default". IMO SQL Server 2005 provides us with tools that allow us to implement optimistic or pessimistic ourselves. I wrote a few examples on simple-talk: Developing Modifications that Survive Concurrency

Edit: I do not think that the default behavior of SQL Server is exactly "pessimistic concurrency control". Let us consider the following simple example, which runs under default isolation level, READ COMMITTED:

-- Connection one

BEGIN TRANSACTION;

SELECT * FROM Schedule 
WHERE ScheduledTime BETWEEN '20110624 06:30:00' 
AND '20110624 11:30' ;

-- Connection two
UPDATE Schedule 
SET Priority = 'High'
WHERE ScheduledTime ='20110624 08:45:00'
-- nothing prevent this update from completing, 
-- so this is not exactly pessimistic

-- Connection one
DELETE FROM Schedule 
WHERE ScheduledTime ='20110624 08:45:00' ;
COMMIT ;
-- nothing prevents us from deleting 
-- the modified row
A-K
  • 16,804
  • 8
  • 54
  • 74
  • Is there no default concurreny control for SQL Server 2005? But when I asked that question explicitly here: http://stackoverflow.com/questions/6459198/default-concurrency-control-for-sql-server-2005 i was told the default was pessimistic concurrency control. Is the answer wrong then? – Sreedhar Danturthi Jun 24 '11 at 08:24