Repeatable read. What is it exactly...

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
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
|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant
|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>

Repeatable read. What is it exactly...

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.
AntRepeatable 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|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>

Repeatable read. What is it exactly...

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.
AntRepeatable 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|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>

Repeatable error, with script, with code

Hello

I have spent the last weeks trying to solve this problem but with no
luck at all, I have a piece of code that looks like this

declare@.bestnr int,
@.artnr varchar(25),
@.journalnrrow int,
@.bestlevant decimal,
@.vb_inpris money

set @.bestnr = 33434
set @.artnr = '1441'
set @.journalnrrow = 11
set @.bestlevant = 50
set @.vb_inpris = 10

insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
bestlevantextqty)
values (@.bestnr, @.artnr, @.journalnrrow, @.bestlevant, @.vb_inpris,
@.bestlevant)

This code works just fine in query analyzer, but in SQL server agent
it does not work it creates an error message about string or bnary
data would be truncated, Simon suggested this has to do with
ansi_warnings being OFF in query analyser and ON when SQL srver agent
makes its connection.

I have scripted my entire DB at
http://donald.fruitsalad.org/microsoft/entiredb.sql

That script creates all the deps and tables and triggers for our DB,
any help would be appreciated, I just don't know where to turn, we
have hired a consultant but that did not help either as he could not
solve the problem.

If anyone know a good sql consultant in sweden that might have a clue
about this, please let us know we are getting pretty desperate.

Regards

MattOn 18 Apr 2004 10:42:00 -0700, Matt wrote:

>Hello
>I have spent the last weeks trying to solve this problem but with no
>luck at all, I have a piece of code that looks like this
>
>declare@.bestnr int,
>@.artnr varchar(25),
>@.journalnrrow int,
>@.bestlevant decimal,
>@.vb_inpris money
>set @.bestnr = 33434
>set @.artnr = '1441'
>set @.journalnrrow = 11
>set @.bestlevant = 50
>set @.vb_inpris = 10
>
>insert into bpl (bestnr, artnr, jibpjournal, bestlevant, vb_inpris,
>bestlevantextqty)
>values (@.bestnr, @.artnr, @.journalnrrow, @.bestlevant, @.vb_inpris,
>@.bestlevant)
>This code works just fine in query analyzer, but in SQL server agent
>it does not work it creates an error message about string or bnary
>data would be truncated, Simon suggested this has to do with
>ansi_warnings being OFF in query analyser and ON when SQL srver agent
>makes its connection.

Hi Matt,

To begin with: Simon (Hayes?) is correct. The reason that you see this
different behaviour is due to the settings Simon mentioned. These
settings are provided for historic reasons.

Previous versions of SQL Server had the feature of trimming strings
that didn't fit the declared length of a variable or column without
notice. For SQL Server 7.0, Microsoft wanted to certify as compliant
with the SQL-92 standard. One of the things this standard proscribed
was that trying to store a string in a columns with a shorter length
is an error condition. In order to provide backwards compatibility,
Microsoft supplied option that allow users to select the amount of
ANSI compliancy. The behaviour on string truncation is governed by the
ANSI_WARNINGS setting.

Try the following in Query Analyzer to see what I mean:

drop table test
go
create table test (try# int, shorttext varchar(6))
go
set ansi_warnings off
insert into test select 1, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
set ansi_warnings on
insert into test select 2, convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
go
select * from test
go

>I have scripted my entire DB at
>http://donald.fruitsalad.org/microsoft/entiredb.sql

Boy, am I glad I'm no longer on a dialup connection - almost 100MB!!
When I saw how long the download would take, I assumed you had lots of
inserts embedded in the script, but I now see that it's just the DDL.
You sure make some pretty complex databases... :-|

>That script creates all the deps and tables and triggers for our DB,
>any help would be appreciated, I just don't know where to turn, we
>have hired a consultant but that did not help either as he could not
>solve the problem.

I killed the osql process that executed your script after more than 30
minutes, as I've already found the cause (though it's absolutely
possible that there are more culprits).

This line is taken from the create table statement for bpl:
[perssign] [varchar] (6) COLLATE Finnish_Swedish_CI_AS NOT NULL ,

This column has a default, specified in the (old and proprietary)
Transact-SQL syntax:

create default DefDbLoginId as convert ( varchar(30) ,
COALESCE (SUser_SName () , 'XX' ) )
GO
(...)
EXEC sp_bindefault N'[dbo].[DefDbLoginId]', N'[bpl].[perssign]'
GO

The default results in a varchar(30) value. You attempt to store this
in a varchar(6) column. That's why the error gets raised if the ANSI
warnings option is set to on.

Some ways to get around this:
a) Change the default DefDbLoginId - replace "varchar(30)" with
"varchar(6)" in the convert function call. (ANSI standards does permit
truncation of strings in explicit conversions).
b) Make sure you always run your code with ANSI warnings set to off.
c) Change the maximum length of bpl.perssign to varchar(30).

(*) Note on ANSI (and other) settings: a default for these settings
can be set per database. This default can be overriden for each
connection. Many tools (like Query Analyzer) will do so - the settings
QA usses for each connection can be found (and changed) via menu
option Query / Currenc Connection Properties and the defaults for new
connection via Tools / Options / Connection Properties. Within the
connection, the setting can be flipped as often as wanted with the SET
command (like I did in the example above).

>If anyone know a good sql consultant in sweden that might have a clue
>about this, please let us know we are getting pretty desperate.

In Sweden? No, sorry. I only know SQL consultants in the Netherlands.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)