Retrieving updates made within a transaction?

[M Wells]

Hi All,

This seems like a tricky question to me. I have a Stored Procedure
that encapsulates a number of updates to various tables within a
transaction.

However, at a later part of the transaction I need to be able to
select records changed by an update statement made earlier within the
same stored proc (and within the same transaction) and need for that
select to reflect the changed values.

My understanding, however, is that the records aren't actually changed
by the update statement until the transaction is committed, and
therefore my later select statement won't return the expected records
because the update is being held until the transaction is committed.

Is this accurate? And, if so, is there a reasonable workaround that
still leaves me able to rollback the entire transaction if I strike a
problem somewhere along the way?

So, a pseudo code example would be:
BEGIN TRANSACTION

UPDATE mytable SET myid = @yourid WHERE myid = @id

SELECT * FROM mytable where myid = @id

COMMIT TRANSACTION

In this above example, would the select statement return the records
that have a myid value of @id as before the update as after the
update?

Many, many thanks in advance!

Much warmth,

Murray

[Hugo Kornelis]

On Thu, 01 Apr 2004 16:26:01 GMT, M Wells wrote:

(snip)

My understanding, however, is that the records aren't actually changed
by the update statement until the transaction is committed, and
therefore my later select statement won't return the expected records
because the update is being held until the transaction is committed.

Is this accurate?
No. The updates are made. If you rollback the transaction later, the
changes will be undone; the log file is used for this. During the
transaction, you'll get to see the changed data.

Other users (or even you yourself on another connection!) won't see
the changed data until the transaction is committed (*). They won't
see the old data either. The affected rows are locked as soon as they
are hit by an update and this lock will remain until the transaction
is either committed or rolled back.

(*) Exception - you can set a transaction's isolation level to "Read
uncommitted" (aka dirty read). This will cause select statements to
defy any locks and just read the "dirty" data - "dirty", since the
transaction may still be rolled back in which case the reading
transaction has read data that never really existed.
So, a pseudo code example would be:
BEGIN TRANSACTION

UPDATE mytable SET myid = @yourid WHERE myid = @id

SELECT * FROM mytable where myid = @id

COMMIT TRANSACTION

In this above example, would the select statement return the records
that have a myid value of @id as before the update as after the
update?

It won't return any rows at all, since the update has just changed to
myid value from @id to @yourid.

Best, Hugo
--

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

[M Wells]

On Thu, 01 Apr 2004 21:28:22 +0200, Hugo Kornelis
<hugo@pe_NO_rFact.in_SPAM_fo> wrote:

BEGIN TRANSACTION

UPDATE mytable SET myid = @yourid WHERE myid = @id

SELECT * FROM mytable where myid = @id

COMMIT TRANSACTION

In this above example, would the select statement return the records
that have a myid value of @id as before the update as after the
update?

It won't return any rows at all, since the update has just changed to
myid value from @id to @yourid.

Hi Hugo,

Thanks for your help! And, laugh, just shows I shouldn't write pseudo
sql before my first cup of coffee.

Thanks again!

Much warmth,

Murray