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 2 2379
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)
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: BFord |
last post by:
Using SQL2000, is there another way of optimizing the original query below.
TIA,
Bob
Update Transaction
set field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder where
tblOrder.id...
|
by: Manoj Sharma |
last post by:
I am performance testing some batch processing engines. These are written in
..NET and SQL Server and are typically concerned with generating XML files out
of data retrieved from the database.
I...
|
by: sebmil |
last post by:
Hello,
I have a table with two columns, created with : CREATE TABLE test ( id
serial primary key, name text ) ;
To populate the table i use :
INSERT INTO test(name) values('test1'); so the...
|
by: Dave Hopper |
last post by:
Hi
I am using the following SQL to retrieve a value in a list box using a
unique ID held in the list box call cntID. The list box is used on an
order form to list appointments that have been...
|
by: Jill Graham |
last post by:
Hi,
What is the best way to resolve following problem in a multi-user
environment ?
I have a table called TOTAL_SALES.
The table has 2 columns : "CarId" and "TotalSales"
I need to access a...
|
by: Leon |
last post by:
How can I code a stored procedure "within my codebehind page" that execute
two or more stored procedures within a transaction?
if so how would I pass values to each parameter?
i.e.
Begin Tran...
|
by: Rico |
last post by:
Hello,
I am in the midst of converting an Access back end to SQL Server Express.
The front end program (converted to Access 2003) uses DAO throughout. In
Access, when I use recordset.AddNew I...
|
by: Dave |
last post by:
I have a form with a label that should show an invoice number. The invoice
number should be generated by sql Server using an autoincremented technique.
However, after reading several articles, it...
|
by: myemail.an |
last post by:
Hi all,
I have a table with this structure:
Customer ID | Transaction date | Transaction type
1 | 1/2/2008 | F
1 | 1/4/2007 | M...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |