March 13, 2009 at 9:00 am
JacekO (3/12/2009)
GSquared (3/12/2009)
That would violate one of the key properties of a database, its ACIDity. Not a good idea. Read up on why ACID matters. It's just about as important as a subject can be in database work.Point proven. No nested transactions in SQL. :w00t:
Based on your definition of "nested transactions", yes, you're right, those aren't in there. Based on the standard definition, they are. So I guess we're both right. 🙂
I think it was Voltair who stated, "before you may argue with me, sir, you must define your words". 🙂
- 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 19, 2009 at 4:37 am
Finally catching up to this thread, now that I have a break from major projectness. Sorry, Lee, that I wasn't able to re-respond earlier.
As far as JacekO's (I think it was him) argument goes about nested procs, I'm a little confused as to where you got your information about nested procs and transactional information?
Even when I was in high school, learning Basic on an Apple IIE, we were taught about transactions and nested processes (not sps, just processes). Using the back account example, which was the major one that everyone understood, you'd never want the money taken out of your savings account and then disappear, never reaching your checking account. You've just lost money that way.
I've spent a lot of time learning computer languages in high school and college, my dad was a Cobol programmer, and now I'm a SQL DBA. In all that time, I've never heard the concept of nesting transactions to have them only partially commit.
Could you enlighten me as to where you got this information? I'd like to research it. See what kind of examples these people have (outside of the ones you've provided) and the logic behind these choices.
Thanks in advance for the information.
March 19, 2009 at 5:52 am
I don't advocate nested tranactions either, I only wanted to perform the transaction in separate SP and then based on their return values, I would commit or rollback as needed.
Don't remember who mentioned nested transactions unless I misunderstand you in the first place
March 19, 2009 at 5:58 am
Lee,
I think you misunderstood me. Nested transactions can be wonderful things, if you need them. If you don't, I don't advise indulging because it's a lot of wasted effort.
What I'm trying to determine is where the idea of having a nested transaction that doesn't 100% rollback when it fails came from. I'd really like to know what platform these people were working on and what data they had at their disposal that made them publish works or train other people that a partial commit on nested transactions were a good thing.
I don't like making snap judgements on things I don't understand. If I can research the background on this, I can judge this whole debate on its true merits instead of allowing myself to be caught up in the emotional argument issue.
Plus, it'll put me in the position of being able to play Devil's Advocate the next time this debate surfaces. @=)
March 19, 2009 at 6:03 am
I don't know or remember - it was one of the respondors to this thread.
March 19, 2009 at 6:07 am
I should also clarify that I don't advocate nested stored procedures (which is different from nested transactions).
I've never had any reason to use them, though, and I know of at least one developer on my team who does use them. So, as far as Nested SPs go, to each their own. @=)
But that thought is different from the above "nested transactions" question I'm trying to figure out.
March 19, 2009 at 6:14 am
I am no longer certain as toe what you mean by nested. I am VFP programmer from way back in the days of dbase. Very littel basic experience.
SQL has GOTO commands but is sorely lacking in loop control [sql2000 or course].
I create SP and SF all the time so that the requests to the sql side is run on the sql side and returns only wht I need.
Originally, I was just trying to refactor some a sql SP into smaller SP to be called from within the main usp instead of using GOTO. Please read the original posting to get some I dea of what I was trying to do
March 19, 2009 at 7:14 am
Nesting is a broad term. You can nest triggers, stored procedures, transactions, IF..Else statements and loops.
Basically, a "nest" is when you embed one thing inside of another and the internal thing completes before the external completes.
So, as far as a loop example goes...
While @Loop1Cnt > 0
Begin --Loop 1, outer loop
While @Loop2Cnt > 0
Begin --Loop 2, Nested internal loop
...some code...
End --Loop 2, Nested internal loop
...some code?...
End --Loop 1, outer loop
Like I said above, you can nest anything. Nesting comes in really handy in a number of circumstances. However, with some objects, it's not always wise to nest them. And if you nest without knowing what you're doing, you can cause a lot more problems than you solve.
Does that make sense?
March 19, 2009 at 7:20 am
Brandie,
Let me clarify few things.
1. In one of the post I stated that SQL does not support nested transactions and GSquared tried 😀 to convice me I am wrong. It all came to the how do we define nested. From my point of view they are not nested but maybe I could call them chained. To explain how I understand the term nested I provided an example in a pseudocode.
2. The need for nested transactions comes handy when you want to log certain exceptions in the database so you know what when wrong and you want to keep this information but you may want to discard the changes the main processing made. In this case you really have two independent processes within one SP call and you may want to commit one and rollback the other and becasue they are independent you practically are not violating the ACID rules.
This might be very difficult to explain without understanding the environment we use the database in. Our database is in a business layer of the application which drives a mechanical device.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 19, 2009 at 8:09 am
am sending the code for my SP. It is severly gutted and memvar, table names, etc have been made more generic. This is due to company policies. It shoiudl hoever, give you and idea of what I am trying to do:
USE [SBT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[MySPName]
@memvar 1char(6) = ''
,@memvar 2 varchar(12) = ''
,@memvar 3 char(5) = ''
,@rtnVal varchar(100) OUTPUT
AS
DECLARE
@errorMessage varchar(500)
,@errorNumber int
,@blnFatalError bit
,@MANYOTHERBOOLEANMEMVARS bit
SET @errorMessage = ''
SET @errorNumber = 0
SET @MANYOTHERBOOLEANMEMVARS = 0
-- let's check what records already exsit
bt_custno))
SET @blnExistsTABLE2Record = (SELECT COUNT(*) FROM DATABASE.OWNER.TABLE 2 WHERE EXISTS (Select * from DATABASE.OWNER.TABLE 2 WHERE proj_id = @memvar 3+'.'+@memvar 1))
--more set follow this for the other tables
BEGIN TRAN
IF @memvar 3 = ''
BEGIN
SET @errorMessage = @errorMessage + 'Fatal Error: Project Code level 1 is missing - Process cannot continue'
SET @blnFatalError = 1
SET @blnProjLevel1Missing = 1
GOTO errorhandler
END
ELSE
BEGIN
GOTO label1
END
label1:
-- 1st check - do we have a customer record in CP
IF EXISTS (Select * From DATABASE.OWNER.TABLE 6 WHERE cust_id = @memvar 2 )
BEGIN
SET @blnExistRecord = 1
-- does the customer address record exist
IF EXISTS (select * from DATABASE.OWNER.TABLE 6 where cust_id = @memvar 2 )
BEGIN
SET @blnExistsRecord = 1
END
ELSE
BEGIN
SET @errorMessage = @errorMessage + 'Warning: Customer Address record for SBT Code: '+@memvar 1+' CP Code: '+@memvar 2+' NOT FOUND in Cost Point' + CHAR(13)+CHAR(10)
END
END
ELSE
BEGIN
SET @errorMessage = @errorMessage + 'Warning: Customer record for SBT Code: '+@memvar 1+' CP Code: '+@memvar 2+' NOT FOUND in Cost Point' + CHAR(13)+CHAR(10)
END
-- CP TRIGGER ISSUE
--If we do not have the appropriate customer info, then we cannot set up
--the TABLE 7_record and the Proj__bill_Info Record
IF @blnExistRecord = 0 or @blnExistsAddrRecord = 0 OR @blnExistsCode = 0
SET @memvar 2 = NULL
label2:
INSERT INTO [DATABASE].[OWNER].
(FIELD LIST....
)
VALUES
(VALUES LIST
)
IF @@ERROR <> 0
BEGIN
Select @errorNumber = @@ERROR
SET @errorMessage = @errorMessage + 'Fatal Error: Insert of Project Code '+@memvar 3+'.'+@memvar 1+' to TABLE 2 Failed' + CHAR(13)+CHAR(10)
SET @blnFatalError = 1
GOTO ErrorHandler
END
ELSE
BEGIN
SET @blnInsertedProj = 1
SET @blnExistsPRecord = 1
GOTO label3
END
label3:
-- now we need to insert a record to theTABLE 3 table
INSERT INTO [DATABASE].[OWNER].
(FIELDS LIST
)
VALUES
(VALUES LIST
)
IF @@ERROR <> 0
BEGIN
Select @errorNumber = @@ERROR
SET @errorMessage = @errorMessage + 'Fatal Error: Insert of Project Code '+@memvar 3+'.'+@memvar 1+' toTABLE 3 Table Failed' + CHAR(13)+CHAR(10)
SET @blnFatalError = 1
GOTO ErrorHandler
END
ELSE
BEGIN
SET @blnInsertedE = 1
SET @blnExistsERecord = 1
GOTO label4
END
label4:
-- now we need to insert a record to theTABLE 4 table table
INSERT INTO [DATABASE].[OWNER].
(FIELDS LIST
)
VALUES
(VALUES LIST
)
IF @@ERROR <> 0
BEGIN
Select @errorNumber = @@ERROR
SET @errorMessage = @errorMessage + 'Warning: Insert of Project Code '+@memvar 3+'.'+@memvar 1+' toTABLE 4 table Table Failed' + CHAR(13)+CHAR(10)
GOTO label5
END
ELSE
BEGIN
SET @blnInsertedR = 1
SET @blnExistsRRecord = 1
GOTO label5
END
label5:
IF @blnExistsCSRecord = 0
AND (@blnExistCRecord = 1 AND @blnExistsAddrRecord = 1 OR @blnExistsCode = 1)
AND (@blnExistsPRecord = 1 AND @blnExistsERecord = 1)
BEGIN
-- now we need to insert a record to theTABLE 4 table table
INSERT INTO [DATABASE].[OWNER].
(FIELDS LIST
)
VALUES
(VALUES LIST
)
IF @@ERROR <> 0
BEGIN
Select @errorNumber = @@ERROR
SET @errorMessage = @errorMessage + 'Warning: Insert of Project Code '+@memvar 3+'.'+@memvar 1+' to TABLE 7 Table Failed' + CHAR(13)+CHAR(10)
GOTO ErrorHandler
END
ELSE
BEGIN
SET @blnInsertSRecord = 1
SET @blnExistsSRecord = 1
GOTO label6
END
END
ELSE
BEGIN
SET @errorMessage = @errorMessage + 'Warning: Insert of Project Code '+@memvar 3+'.'+@memvar 1+' to TABLE 7 Table ' + CHAR(13)+CHAR(10)
SET @errorMessage = @errorMessage + 'NOT done due to insufficient Customer information for CP Customer Code '+@memvar 2+ + CHAR(13)+CHAR(10)
GOTO ErrorHandler
END
label6:
-- this will be ignored if the TABLE 7 record is not inserted
-- now we need to insert a record to the TABLE 5 table
INSERT INTO [DATABASE].[OWNER].
(FIELDS LIST
)
VALUES
(VALUES LIST
)
IF @@ERROR <> 0
BEGIN
Select @errorNumber = @@ERROR
SET @errorMessage = @errorMessage + 'Warning: Insert of Project Code '+@memvar 3+'.'+@memvar 1+' to TABLE 5 Table Failed' + CHAR(13)+CHAR(10)
GOTO ErrorHandler
END
ELSE
BEGIN
SET @blnInsertedB = 1
SET @blnExistsBRecord = 1
GOTO Finis
END
ErrorHandler:
-- NOTE I have remo e the RAISEERROR since the message has to be returned
--to my VFP program so there is no need to raise an error here, the logging takes place in the VFP
--program
-- let us enhance the error message
-- do I need to RAISERRROR
-- RAISERROR('We have an error %d',10,1,@errorMessage)
--Print 'Had an Error'
GOTO finis
Finis:
IF @blnFatalError = 0
-- we have not Fatal errors so we can commit the changes
BEGIN
SET @errorMessage = 'Successfully created project code '+@memvar 3+'.'+@memvar 1
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
-- now we need to be able to return some information to Calling system
-- For a fatal error there can be a mx of two errors only -
--however, there can be a max of 3 warining messages
--See code for exact message text
Select @rtnVal = @errorMessage
Select @rtnval as usrerrorMessage
,@blnFatalError as fatalError
,and the rest of the booleans are also placed into this statement
the above produces a cursor that is returned to VFP with the results i need
March 19, 2009 at 8:14 am
JacekO (3/19/2009)
From my point of view they are not nested but maybe I could call them chained. To explain how I understand the term nested I provided an example in a pseudocode.
JacekO, I don't want to just look at your pseudocode. I want to know where you got these definitions and who taught them to you.
Please give me references, book titles, names, websites and such so I can go back and see why you believe the way you do.
I'm not saying you're wrong and GSquared is right. I'm saying, I want to know the source of your understanding. Every SQL person I've ever met defines nested transactions the way GSquared does. I want to see where your information came from.
Please provide more information so I can research. Thanks, @=)
March 19, 2009 at 8:18 am
Lee,
Before I go any further, Label 1 confuses me.
label1:
-- 1st check - do we have a customer record in CP
IF EXISTS (Select * From DATABASE.OWNER.TABLE 6 WHERE cust_id = @memvar 2 )
BEGIN
SET @blnExistRecord = 1
-- does the customer address record exist
IF EXISTS (select * from DATABASE.OWNER.TABLE 6 where cust_id = @memvar 2 )
BEGIN
SET @blnExistsRecord = 1
END
ELSE
BEGIN
SET @errorMessage = @errorMessage + 'Warning: Customer Address record for SBT Code: '+@memvar 1+' CP Code: '+@memvar 2+' NOT FOUND in Cost Point' + CHAR(13)+CHAR(10)
END
END
Why are you setting the same variable twice with the same exact code and then having it nested?
March 19, 2009 at 8:25 am
ignore that kind of thing - I was jsut trying to remove the sensitive information, rest assured that there is nothing like that occuring in the code - please assume that all memvars, labels and tables are different
March 19, 2009 at 8:26 am
Brandie,
No offence but few posts before you gave a definition of nesting. You explained what nesting is. My SQL pseudocode was in a sense a transformation of your pseudocode into the SQL syntax with translations.
The SQL Server transactions can be called nested transactions but they are not. Plain and simple. If they were nested they should work the way I described. There might be reasons why they can not be trully nested but this is not the point.
So there is no reference or a book to be looked at.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 19, 2009 at 8:26 am
please remember that my company is still using sql2000 - so WHILE does not exist for me
Viewing 15 posts - 76 through 90 (of 99 total)
You must be logged in to reply to this topic. Login to reply