1

I'm getting conflicting answers for this question. I got an answer that SQL Server 2005 uses pessimistic concurrency control as default here, but this answer gets refuted here (see the second reponse) that SQL Server does not have any default concurrency control and it merely gives the mechanisms to implement either an optimistic or pessimistic concurrency control. Whom should i believe and can some one substantiate their answer with an authoritative source.

Thanks in anticipation.

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

2 Answers2

2

SQL Server uses pessimistic concurrency by default

This is stated categorically in the offical MS documention...

To change to optimistic for SQL Server 2005+, you need to enable Snapshot Isolation

gbn
  • 422,506
  • 82
  • 585
  • 676
  • I wanted to comment on your answer, but the comment just got too big, so I added another answer. – A-K Jun 24 '11 at 15:56
2

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

Regarding the following statement from the link posted by gbn: "Historically, the concurrency control model in SQL Server at the server level has been pessimistic and based on locking.", my understanding of what it means is this: prior to 2005 only the tools to implement pessimistic concurrency were provided. Yet we still needed to up the isolation level to achieve pessimistic concurrency, it did not and does not occur by default.

I might be wrong, of course. I have sent Kalen Delaney, the author of that MSDN article, a link to this thread. Hopefully she could find a few minutes to comment.

Edit: here is the MSDN definition: "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." Clearly this is not happening by default, as I have shown in my example - the shared lock is released after the row has been read.

A-K
  • 16,804
  • 8
  • 54
  • 74
  • pessimistic for write is probably more accurate. The first SELECT doesn't persist locks because of ISOLATION LEVEL, the 2nd UPDATE is atomic and not in an explicit transaction. Hence we get the concurrency errors that can be removed by REPEATABLE_READ or SERIALIZABLE – gbn Jun 24 '11 at 16:00
  • @gbn: according to MSDN, true pessimistic control should persist a shared lock in my scenario – A-K Jun 24 '11 at 16:23
  • @gbn @alexKuznetsov so is there no default concurrency control in SQL Server 2005? – Sreedhar Danturthi Jun 25 '11 at 08:02
  • @user653622: I would say yes there is, but "pessimistic for write". Note that @AlexKuznetsov is saying Microsoft's documentation is wrong too... – gbn Jun 25 '11 at 08:31
  • @user653622 the default behavior is neither pessimistic nor optimistic concurrency control. – A-K Jun 26 '11 at 15:32
  • @AlexKuznetsov http://msdn.microsoft.com/en-us/library/cs6hb8k4(v=VS.80).aspx in this see the section "Concurrency Control in ADO.NET and Visual Studio". It is specifically written about ADO.NET and Visual Studio as "ADO.NET and Visual Studio use optimistic concurrency, because the data architecture is based on disconnected data. Therefore, you need to add business logic to resolve issues with optimistic concurrency." Can we say SQL Server uses optimistic concurrency by default to catch up with the ADO.NET? – Sreedhar Danturthi Jun 26 '11 at 16:12
  • @user653622: I would put it the other way around: ADO.Net uses some features of SQL Server to implement optimistic concurrency. – A-K Jun 26 '11 at 18:04