transaction not rolling back
-
Hi, I have this procedure below, the tblInvoices table has a unique value index on the invoicedate.
I assumed that when a second insert was attempted it would error and cause the tranaction to rollback. But it doesn't ??
The first statement within the trans fails due to a violation of the unique value index, but then the next statement performs it's inserts and they aren't rolled back? What am I missing? Maybe I have to save the @@error to a var after every statement, then at the end evaluate that var to see if there was errors?
declare @newInvoice int
begin tran
insert into tblInvoices (invoicedate) values (dbo.monthStart(getdate()))
set @newInvoice = @@identity
insert into tblInvoiceItems (invoice_fk,company,ppname,reportplan,amount)
SELECT TOP 100 PERCENT @newInvoice,dbo.tblClient.company, dbo.tblClientPP.Publishing_point, reportingplan,
case reportingplan
when 0 then 7.5
when 1 then 12.5
when 2 then 37.5
when 3 then 37.5
end
as fee
FROM dbo.tblClient INNER JOIN
dbo.tblClientPP ON dbo.tblClient.uid = dbo.tblClientPP.client_fk
WHERE (dbo.tblClient.startdate < GETDATE())
ORDER BY dbo.tblClient.companyupdate tblInvoices set total=(SELECT sum(
case reportingplan
when 0 then 7.5
when 1 then 12.5
when 2 then 37.5
when 3 then 37.5
end
)
as fee
FROM dbo.tblClient INNER JOIN
dbo.tblClientPP ON dbo.tblClient.uid = dbo.tblClientPP.client_fk
WHERE (dbo.tblClient.startdate < GETDATE())
)
where uid = @newInvoiceif (@@error <> 0)
rollback
else
commit
select @@error
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
> Maybe I have to save the @@error to a var after every statement, then at the end evaluate that var to see if there was errors?
Yes, the @@error value is only present for the one statement immediately following the statement to which it belongs. Just add the values to a local variable and then rollback if that variable is finally not zero.
--Jonathan -
OK after playing around with it a few times I think i have it. I actually need two variables.
declare @DidItError int
declare @AnyErrors bit
insert statement ....
set @DidItError = @@error
if @DidItError 0 then
@anyErrors = 1
update....
set @DidItError = @error
if @DidItError 0 then
@anyErrors = 1
if @anyErrors = 1 then
rollback
else
commit
==========================================
took me a minute to figure out why this wasn't working:
set @success = 0
if @@error 0 then
set @success = @@error
...
if @success 0
===============================
A successful check of the error object, reset it to 0.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
No, as I wrote, you only need one local variable:
declare @DidItError int
insert statement ....
set @DidItError = @@error
update....
set @DidItError = @DidItError + @@error
if @DidItError > 0 then
rollback
else
commit
--Jonathan -
ya as soon as I started to update the proc my brain turned back on
Thanks again,
dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Hi All,
I have a question on Transaction commit/rollback.
Question
My understanding is that untill a transaction is comitted, the modifications(inserts/updates/deleted) are not made on th disk ( datafiles).
For Example if i am doing a bulk copy of millions of records from a flat file on to sql server table, untill all the records are bulkcopied and transaction completes successfully and a commit is issued explicitly and the data is wriiten to disk where is the data stored till then?
i mean where is the data stored till the transaction is completed and committed ? This could be really a large data..?
Please let me know
Thanks,
THNQdigital
-
Since you're going to roll back if any errors happen, you don't need an error variable:
insert statement ....
If @@ERROR <> 0
ROLLBACK ---no need to go further!
update statement ....
If @@ERROR <> 0
ROLLBACK
ELSE
COMMIT
If you want the calling routine to know which statement bombed out, use RETURN to send back a different value for each error.
Dana
Connecticut, USA
Dana - My understanding is that untill a transaction is comitted, the modifications(inserts/updates/deleted) are not made on th disk ( datafiles).Your understanding is wrong.SQL Server will write changes to disk at any time to relieve pressure on the buffer cache. However, before writing to disk it will write both the old page and the new page to the transaction log. In this way, if your transaction fails, SQL can read the transaction log to get the old pages back and return the data files to their original state.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Quotation from Inside SQL Server 2000:
Explicit and Implicit Transactions
By default, SQL Server treats each statement-whether it's dispatched individually or as part of a batch-as independent and immediately commits it. Logically, the only kind of statement that can be committed is a statement that changes something in the database, so talking about committing a SELECT statement is really meaningless. When we look at transaction isolation levels later in this section, you'll see that it's important in some cases to include SELECTs inside larger transactions. However, when we talk about transactions, we're generally talking about data modification statements (INSERT, UPDATE, and DELETE). So any individual data modification statement by itself is an implicit transaction. No matter how many rows are modified, they will either all be modified or none of them will be. If a system failure occurs while a million-row table is being updated, when SQL Server recovers, it will roll back the part of the update that's already been written to disk, and the state of the database will be as if the update never happened.
If you want a transaction to include multiple statements, you must wrap the group of statements within BEGIN TRANSACTION and COMMIT TRANSACTION or ROLLBACK TRANSACTION statements. Most of the examples in this section will deal with transactions containing multiple statements, which are called explicit transactions.
You can also configure SQL Server to implicitly start a transaction by using SET IMPLICIT_TRANSACTIONS ON or by turning on the option globally using sp_configure 'user options', 2. More precisely, you take the previous value for the user options setting and OR it with (decimal) 2, which is the mask for IMPLICIT_TRANSACTIONS.
For example, if the previous value were (decimal) 8, you'd set it to 10 because 8|2 is 10. (The symbol used here is the vertical bar, not a forward or backward slash.) If bit operations such as 8|2 are somewhat foreign to you, let SQL Server do the work. You can issue a SELECT 8|2 in SQL Query Analyzer, and the value returned will be 10. (But be careful not to assume that you just add 2 to whatever is already there-for example, 10|2 is 10, not 12.)
If implicit transactions are enabled, all statements are considered part of a transaction and no work is committed until and unless an explicit COMMIT TRAN (or synonymously, COMMIT WORK) is issued. This is true even if all the statements in the batch have executed: you must issue a COMMIT TRANSACTION in a subsequent batch before any changes are considered permanent. The examples in this book assume that you have not set IMPLICIT_TRANSACTIONS on and that any multistatement transaction must begin with BEGIN TRAN.
But it can also be found in BOL
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
HI,
"However, before writing to disk it will write both the old page and the new page to the transaction log"
This is a very interesting statement...What is before writing to disk.. is it before writing on to datafiles?..
If yes, this is what i want to know in prescise , will it use data file or transaction log file, before the data is made permanent on the disk (datafile where the table is located)
i mean.. can the sql server use space in the datafile before the transaction is committed to store the data temporarily and do not make it permanent if the transaction is incomplete?
or is it only the transaction log file ( this is also ofcourse on the disk) that is used by sql server to stored the data temporarily before actually writing the data on to datafiles permanently..
i believe it would use only transaction log file.. just wanted to know if it is true.
please let me know. Thanks all
Best Regards
THNQdigital
-
I should have said 'before writing to the data file it will write ... to the transaction log'.
SQL Server will write updated pages to the data files before the transaction is committed, if it needs to relieve pressure on the buffer cache. Prior to writing to the data files, it will write the old and the new version of the page to the transaction log to allow a ROLLBACK to be done.
This is done on the assumption that perhaps 99% of transactions issue a COMMIT and not a rollback. Therefore, SQL Server is giving maximum efficiency to transactions that COMMIT at the expense of transactions that ROLLBACK.
In fact, SQL Server simply could not operate if it only wrote uncommitted pages to the transaction log. If it did do this, at commit time it would have to read the log to obtain all the updates. (For large transactions this could be many MB of log to read.) It would then have to apply those pages to the data files. In order to allow the apply to roll back, it would at this point have to write a new copy of the old page to the transaction log before it could write the new page to the data files. Eventually it would apply all updates. All this work would take place during the execution time of the COMMIT statement. The apply must be serialised within the COMMIT to allow for the possibility of the apply to fail.
If it tried to keep all updated pages in memory only, then the number of updates you could do in a transaction would be limited by the amount of memory on your machine.
Therefore, it is easy to see that pressure on the buffer cache will cause updated but uncommitted pages to be written to the data files, bcause there is no other way to do this work.
Just about every database system on the market uses this concept. Every copy of SQL Server, Oracle, Sybase, etc that is sold triggers a license payment to IBM, who hold the patent on this technology.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Thanks grasshopper that seems like the easiest way. Unless issueing the rollback/committ stops the proc then wouldn't I also need something like:
insert statement ....
If @@ERROR 0
ROLLBACK ---no need to go further!
GOTO THEEND
update statement ....
If @@ERROR 0
ROLLBACK
ELSE
COMMIT
THEEND:
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Hi!
You really should take a look at
SET XACT_ABORT:
Specifies whether Microsoft® SQL Server™ automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
Regards, Hanslindgren!
-
Regarding your statement: "Unless issueing the rollback/committ stops the proc ... " - doesn't it? I've always assumed that once you rollback, that's the end of it. Note my additions (in red) to your code snippet:
BEGIN TRANSACTION
insert statement ....
If @@ERROR <> 0
BEGIN
ROLLBACK ---no need to go further!
update statement ....
If @@ERROR <> 0
ROLLBACK
ELSE
COMMIT
END TRANSACTION
Dana
Connecticut, USA
Dana -
Hit submit too soon ... disregard previous entry.
Regarding your statement: "Unless issueing the rollback/committ stops the proc ... " - doesn't it? I've always assumed that once you rollback, that's the end of it. Note my additions (in red) to your code snippet:
BEGIN TRANSACTION
insert statement ....
If @@ERROR <> 0
BEGIN
ROLLBACK ---no need to go further!
RETURN <some return code> -- exit the stored procedure
END
update statement ....
If @@ERROR <> 0
BEGIN
ROLLBACK
RETURN <some return code>
END
ELSE
BEGIN
COMMIT
RETURN 0
END
END TRANSACTION
Dana
Connecticut, USA
Dana
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply