Concurrent transaction in READ COMMITED isolation level

Hello,
I currently work on Sql Serveur transactions (not distributed) and their
behaviors according to the selected isolation level.
I try to reproduce the default working of Oracle, and I have the following
problem:
With an isolation level READ COMMITED, by default Oracle "lock" no
request for reading modified records in another transaction (or connection)
not yet validated. Set apart the modifications made in the transaction in
progress, the result of a SELECT always return the "version" of the
validated recordings. Thus, there is no blocking in reading.
Sql Server 2000 function differently with the same isolation level. A
request SELECT is systematically blocked if the set of result must contain a
record new or modified not yet validated. Key word READPAST in request
SELECT solves only the problem related to the new records, the modified
record always block the request for reading!
Is there a solution?
Thank you in advance for your assistance.The key in either situation is to hold locks as short of a time span as
possible. READ COMMITTED puts a lock on an object, does it's thing with the
object, drops the lock on the object, and then moves to the next object
doing the same thing to the next object (usually we are talking about a row
in a table.)
So, in a well built system, the only contention is when you have a lot of
writes to rows that people also are trying to look at. So first question,
do you have people who need to see the data that people are actually
actively modifying? If so, then you do have a quandry. If not (and usually
not) then you may have indexing problems where you are scanning an entire
table instead of just some rows.
This is the place to start, but the crux of it all is simple, separate
readers from writers as much as possible, hold locks for as short a time
period as possible. It will be the same in 2005 when we get versioning as
well. The longer the locks, the more likely we get inconsistency and or
locking.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Christopher" <Dev@.effect.fr> wrote in message
news:utLZFZkWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I currently work on Sql Serveur transactions (not distributed) and their
> behaviors according to the selected isolation level.
> I try to reproduce the default working of Oracle, and I have the following
> problem:
> With an isolation level READ COMMITED, by default Oracle "lock" no
> request for reading modified records in another transaction (or
> connection) not yet validated. Set apart the modifications made in the
> transaction in progress, the result of a SELECT always return the
> "version" of the validated recordings. Thus, there is no blocking in
> reading.
> Sql Server 2000 function differently with the same isolation level. A
> request SELECT is systematically blocked if the set of result must contain
> a record new or modified not yet validated. Key word READPAST in request
> SELECT solves only the problem related to the new records, the modified
> record always block the request for reading!
> Is there a solution?
> Thank you in advance for your assistance.
>|||Thank you for this explanation.
Just a precision according to my context:
I don't know if some people need frequently to see the data that others
are actually modifying, but it's a possibility in any business application.
It can be a problem in some situations.
However, in all cases, I'm agree that it's better to hold locks for a
short time (short transaction).
Christopher.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message de
news: OIIrzzlWFHA.132@.TK2MSFTNGP14.phx.gbl...
> The key in either situation is to hold locks as short of a time span as
> possible. READ COMMITTED puts a lock on an object, does it's thing with
> the object, drops the lock on the object, and then moves to the next
> object doing the same thing to the next object (usually we are talking
> about a row in a table.)
> So, in a well built system, the only contention is when you have a lot of
> writes to rows that people also are trying to look at. So first question,
> do you have people who need to see the data that people are actually
> actively modifying? If so, then you do have a quandry. If not (and
> usually not) then you may have indexing problems where you are scanning an
> entire table instead of just some rows.
> This is the place to start, but the crux of it all is simple, separate
> readers from writers as much as possible, hold locks for as short a time
> period as possible. It will be the same in 2005 when we get versioning as
> well. The longer the locks, the more likely we get inconsistency and or
> locking.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Christopher" <Dev@.effect.fr> wrote in message
> news:utLZFZkWFHA.2420@.TK2MSFTNGP12.phx.gbl...
>|||> I don't know if some people need frequently to see the data that others
> are actually modifying, but it's a possibility in any business
> application.
It is a possibility, though I find in quite a few cases blocking comes from
poor planning for separation rather than a need for two people to work on
the same rows.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Christopher" <Dev@.effect.fr> wrote in message
news:ORK5XV4WFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Thank you for this explanation.
> Just a precision according to my context:
> I don't know if some people need frequently to see the data that others
> are actually modifying, but it's a possibility in any business
> application.
> It can be a problem in some situations.
> However, in all cases, I'm agree that it's better to hold locks for a
> short time (short transaction).
> Christopher.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message
> de news: OIIrzzlWFHA.132@.TK2MSFTNGP14.phx.gbl...
>