CODEWITHSUNDEEP
sql-serverconcurrency
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7601

which concurrency control is more efficient pessimistic or optimistic concurrency control

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

Upvotes: 1

Views: 1554

Answers (2)

A-K
A-K

Reputation: 17090

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

Upvotes: 1

therealmitchconnors
therealmitchconnors

Reputation: 2760

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.

Upvotes: 2

Related Questions