Re: Repeatable read. What is it exactly...

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 12/01/04 

Date: Tue, 30 Nov 2004 19:45:03 -0800

Repeatable read and serializable are very similar, and your question is far
from stupid.

In repeatable read, SQL Server will hold locks on the data you have read
until the end of the transaction. (Normally, in read committed, locks on
data you have read are released as soon as you are done reading.) So no one
can change change the data until you commit or rollback. Updates and deletes
are not permitted.

Serializable prevents INSERTS of new data that satisfy a condition that you
used for searching.

Example

BEGIN TRAN

SELECT * FROM titles
WHERE price < 5

-- suppose the above returns 2 rows; in repeatable read, those two rows are
now locked and no one can change them
-- until you commit or rollback; but someone in another connection could
insert a whole new row with price < 5

-- So, in serializable you are absolutely guaranteed that if you run the
exact SELECT again, you will get the same result:

SELECT * FROM titles
WHERE price < 5

-- But, in repeatable read, you might get more rows back, because of
inserts.

COMMIT TRAN

In repeatable read, SQL Server locks the data you read.
In serializable, SQL Server locks the data and the 'gaps' between the data. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@discussions.microsoft.com> wrote in message 
news:78571FC8-E575-4D1C-BA59-E07C2C176A80@microsoft.com...
> Sorry for such a basic question but what exactly is a Repeatable read as 
> far
> as transaction Isolation levels go. I understand the concept of 
> serializable,
> read committed & read uncommitted but i can't find any documentation that
> describes the repeatable read level. How does this isolation level affect
> concurrency. Sorry for such a stoopid question but it woud really help if 
> I
> got the low down once & for all. Many thanks in advance.
> Ant

Relevant Pages

  • ISOLATION levels and when to use SERIALIZEABLE
    ... It seems to me that isolation levels only affect reads ... If the transaction contains only INSERT, UPDATE or DELETE, isolation level ... doesn't matter since they all use X locks. ... COMMIT -- or ROLLBACK ...
    (microsoft.public.sqlserver.programming)
  • Re: row vs page locking...
    ... so they automatically escalate to page level locks. ... 'Lock Escalation' - see Books Online. ... SQL Server 2005 is going to have a Snapshot ... Good old fashioned locking is less sexy, but I find, more productive! ...
    (microsoft.public.sqlserver.server)
  • Re: Locking in SQL Server and Oracle
    ... I am glad you would like to stay with SQL Server :-) ... > hard it would be to convert to Oracle during our next PeopleSoft upgrade. ... > had thought that since databases are databases, tables are tables, and SQL ... > uses row-level locks exclusively. ...
    (microsoft.public.sqlserver.server)
  • Re: Need help with DAO and SQL Server locking issue
    ... I will forward your response to the client. ... > Absolutely nobody uses DAO to work with SQL Server, ... I'd advise moving code that uses recordsets ... >>Locks are being placed on tables inside of a transaction each time the ...
    (microsoft.public.sqlserver.odbc)
  • isolation levels
    ... I am a developer developing web applications using ASP.Net, PHP and SQL Server. ... I did connect to the sql server and opened query analyser and then selected Northwind db and wrote the batch below ... (I realise that this will commit in milliseconds, that is why I did not commit it-assuming I had a different query inside the transaction that it will take 15 seconds to commit) ...
    (microsoft.public.sqlserver.programming)