March 12, 2009 at 11:22 am
GSquared,
You presented a nice case for sequential transactions. I need a good case that will prove that the nesting the way it is implemented really works and is usefull. Why would I want to commit inner transaction if it can be rolled back later on.
I do not want to argue, I just need to understand if there is any value in the way it is implemented.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 12:01 pm
Beginners should be aware that the examples given above are just for illustrative purposes.
There is no point in encapsulating a single UPDATE statement alone in a transaction. The UPDATE statement will either succeed on all recrods involved in the set being manipulated or it will fail altogether. For instance, if a bank is transferring money from one account to another. Either both updates work or leave the initial account alone.
BEGIN TRAN
INSERT INTO Account (AccountID, Date, isDebit, Amount)
SELECT SourceAccountID, GETDATE(), 1, 123.45
FROM ...
IF @@ERROR <> 0 SET @li_RetCode = -1
IF RetCode = 0 BEGIN
UPDATE Account
SET Balance = Previous_Balance + CASE WHEN isDebit = 1 THEN - Amount ELSE AMOUNT END
WHERE AccountID = SourceAccount
IF @@ERROR <> 0 SET @li_RetCode = -2
END
And the corresponding operations on the Destination Account
IF @@ERROR <> 0 COMMIT TRAN ELSE ROLLBACK TRAN
The point behind a transaction is that a sequence of SQL UPDATE / INSERT / DELETE statements executed one after the other must ALL succeed. Now if there is only one such statement in a transaction, then there is not much point in using a transaction.
And yes, in the end, I too would like to have a scenario where nested transactions would be useful.
March 12, 2009 at 12:04 pm
-> JacekO
3. There is an issue realted to passing the data between ths SPs. Remember whenever you use OUTPUT parameter to retrieve a piece of information from the called SP that SQL treats that parameter as both input and output so if you are not carefull you might pass someting into your SP you expected not to be there.
In the case where a table is being updated (say 1,000 records) an OUTPUT parameter might not be sufficient. And if you are working on a temp table, short of declaring it a global temp table (##T instead of just #T), this is getting more complex.
Regards
March 12, 2009 at 12:17 pm
J
I just pointed out that the OUTPUT in SQL implementation is not a pure output parameter and can cause issues if not used carefully.
In most cases (depending on your design and needs) you do not have to use OUTPUT params at all, and when the SP is dealing with multiple records I would not move them around between SPs at all - just update/delete/insert what you need and if the other SP needs to do more work let it deal with the updated data in the tables.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 12:24 pm
JackedO
Agrred.
I have this one case when closing a work order to assemble and deliver merchandise to a customer. The stored proc creates one invoice per store in the chain.
IF a customer wants invoices regrouped by distribution centers, then a second stored procedure is called to create the master invoice(s). That second takes the newly created invoices and sums up the quantities and extended amounts, i.e. it is working on the same invoice table. Hence there is no need to pass data between the two stored procedures.
By the way, I have yet to find a practical use for the OUTPUT parameter(s).
Regards
March 12, 2009 at 12:28 pm
Basically, it allows you to have slightly finer control over complex stored procedure transactions.
Say, for example, you have a stored procedure that inserts some data into a table, then calls another stored procedure that does some stuff in other tables based on what you just inserted.
There are a couple of possible things you might want to do with regard to transactions. Perhaps, no matter what comes up, you want to keep the row you started with. That would be a good case for sequential transactions. On the other hand, you might want to make it so that, if something comes up on one of the other tables, you want to undo that first insert, that would be a good case for nested transactions. It all depends on what you want to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2009 at 12:37 pm
For example just two of the cases when I use OUTPUT params:
1. Sometimes when returning one or few fields back to the UI for one record because it is less expensive to consume the OUTPUT param then a recordset.
2. When I have SPs that evaluate certain conditions (for example based on the configuration settings which we store in the database) and could be used by multiple other SP. The result of such evaluation could be returned back and consumed by other SP. You could argue that one could use UDFs for this but because UDFs could be used inline in the SELECT statements I tend to avoid UDF that read too much data from the tables.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 12:48 pm
Sorry GSquared, no offense, but I am not buying this. Your explanation is very theoretical and does not provide an example when 'nested' transaction is useful. Using your second example I would just use one transaction and that is it. Why would I need to use nested transactionsin such a case?
I guess I would need some code example with explanation on the points when using nested transactions makes a difference. And if you do not have something handy please do not spend too much time making it up.;)
We all have other work to do...(the one that pays the bills :hehe:)
Maybe there is someone else reading this thread who can come up with an example?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 12:48 pm
Regarding transactions, you can use SAVE POINTS to avoid ROLLBACK undoing all your work. You can do this to nest your transactions, and yet ensure that some of the work that you do remains even if further processing would fail for some reason.
March 12, 2009 at 12:50 pm
JacekO
Is the OUTPUT parameter better than using a return value ?
Of course if you need more than one value, then this is not readily doable with a single-valued return code. But if you have just one record, you could do a select for the single record of interest ?
March 12, 2009 at 1:01 pm
J,
Return can only return INT values, so if you need to get back something else it does not work. Also I would use return to indicate if the SP failed or not rather the to pass values.
Returning the values as OUTPUT params is less expensive then building a recordset - even if the recordset contains one row. I think (but might not be exactly correct on this one) that is less expensive on both ends - the SQL and the UI (C#,VB or so).
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 1:04 pm
JacekO
Thanks for you response.
March 12, 2009 at 1:25 pm
Lynn,
If I am not mistaken the SAVE POINTS still does not provide you with an option to nest. It allows you to rollback to a certain point but it is still sequential. By nesting I mean something like this
BEGIN TRANSACTION 1
do work A
do work B
BEGIN TRANSACTION 2
do work C
do work D
COMMIT TRANSACTION 2
do work E
ROLLBACK TRANSACTION 1
work C and work D is COMMITED
work A, work B and work E is ROLLED BACK
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 12, 2009 at 1:36 pm
JacekO (3/12/2009)
Lynn,If I am not mistaken the SAVE POINTS still does not provide you with an option to nest. It allows you to rollback to a certain point but it is still sequential. By nesting I mean something like this
BEGIN TRANSACTION 1
do work A
do work B
BEGIN TRANSACTION 2
do work C
do work D
COMMIT TRANSACTION 2
do work E
ROLLBACK TRANSACTION 1
work C and work D is COMMITED
work A, work B and work E is ROLLED BACK
If I understand transactions correctly, this would violate the ACID properties of transactions. If TRANSACTION1 needs to rollback, so does TRANSACTION2 as it is encapsulated by TRANSACTION1.
Now, this is more of what I was thinking:
BEGIN TRANSACTION1
BEGIN TRANSACTION2
do work A
do work B
SAVEPOINT A
BEGIN TRANSACTION3
do work C
do work D
BEGIN TRANSACTION4
do work E
COMMIT TRANSACTION4
do work F
--- ERROR OCCURS
ROLLBACK TRANSACTION3 TO SAVEPOINT A
COMMIT TRANSACTION2
COMMIT TRANSACTION1
work A and work B are saved, all other work is rolled back.
March 12, 2009 at 1:41 pm
Yup !
If Tran_2 is within Tran_1, then rolling back Tran_1 also rolls back Tran_2 even though Tran_2 has been committed.
This is the whole concept of @@TRANCOUNT.
Viewing 15 posts - 46 through 60 (of 99 total)
You must be logged in to reply to this topic. Login to reply