Few confusing things about transactions
in [Programming SQL]
|
From: trubar a on 7 May 2010 18:08 On May 6, 3:08 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > > c) Anyways, again assuming transaction is already opened when we call > > SP, why couldnt/shouldnt transaction begin outside SP and end inside > > SP ( end inside SP either via rollback or commit )? > > If @@trancount has a different value when the procedure is exited, this > raises error 266, so committed the caller's procedure is not good. Yes, but why was such an implementation chosen? Thus, why did designers decide that it should be better if transaction started outside SP couldnt end inside SP? > Rolling it back, also produce the error, but there are situations when > you have no other choice. > So youre saying that even though in most situations transaction started outside SP cant be rollback inside SP, there are exceptions when this is allowed and thus no error is raised?
From: Erland Sommarskog on 8 May 2010 13:24 trubar a (asponmynet(a)gmail.com) writes: > Yes, but why was such an implementation chosen? Thus, why did > designers decide that it should be better if transaction started > outside SP couldn�t end inside SP? Now you are asking for archeological excavacations into the layers 1980s in California... (That is, Sybase.) But I think it is a sound principle. A stored procedure is a module, and the module should own the transaction. If you start a transaction in one procedure A and then calls B which commits the procedure, your modules are tightly coupled, which is not good software engineering. But note that the check is on @@trancount - so, if A starts a transaction, and then calls B which performs COMMIT TRANSACTION followed by BEGIN TRANSACTION, there will be no error raised on exist. And while it is raised as an error, it is intended to be a warning. Most of the time when you get this error, the reason is that you have a stray BEGIN/COMMIT TRANSACTION or one too few. But error 266 has some very weird characteristcs. And to make it even fun - the error is not raised if the procedure is called from a trigger. (And here MS cannot blame Sybase, this was something MS introduced on their own.) >> Rolling it back, also produce the error, but there are situations when >> you have no other choice. > > So you�re saying that even though in most situations transaction > started outside SP can�t be rollback inside SP, there are exceptions > when this is allowed and thus no error is raised? No, you will always get error 266 when you exit a procedure with a different value of @@trancount (save the trigger exception noted above). However, in this situation you have typically already received some other error, so 266 is just some noise on the wire. Error and transaction handling in SQL Server is a complext topic, not because there are so many inconsistencies. And even if the addition of TRY-CATCH in SQL 2005 was a big improvement, there are still a lot of funny things when you scratch the surface. On my web site, you can find articles on error handling. They mainly aim at SQL 2000; the corresponding article for SQL 2005 is still in the works. Nevertheless, those articles can give you some idea of what's going on. http://www.sommarskog.se/error_handling_2005.html -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: trubar a on 10 May 2010 13:50 Hi, I know Im taking your time, so I will wrap it up as quickly as possible On May 8, 10:24 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > But note that the check is on @@trancount - so, if A starts a transaction, > and then calls B which performs COMMIT TRANSACTION followed by BEGIN > TRANSACTION, there will be no error raised on exist. I assume that too is considered a bad programming practice? 2) This is from one of your earlier posts: > 2) Lot's of errors in SQL Server causes the transaction to be "doomed"; > that is, if there is no TRY-CATCH, the batch and transaction is > aborted directly. If there is a CATCH handler, you can only roll it > back entirely. Im not quite sure what you mean by batch and transaction being aborted directly?! For example: * if I run the following SP without the TRY-CATCH and if the DELETE causes an error, then statements ( in our example INSERT statement ) following DELETE statement will still get executed: CREATE PROCEDURE some_SP AS DELETE FROM A WHERE a1 = 1; INSERT INTO A VALUES(1,'A') ; Same thing happens if the two statements are executed directly inside a batch. In other words, if batch or SP was aborted when DELETE statement caused an exception, then INSERT wouldnt get executed. * Similarly, if the DELETE caused an exception inside transaction, the two INSERT statements would still get executed AND committed: CREATE PROCEDURE some_SP AS BEGIN TRANSACTION INSERT INTO A VALUES(1,'inside transaction') DELETE FROM A WHERE a1 = 1; INSERT INTO A VALUES(2,'inside transaction') COMMIT BTW if I understand you correctly, then if SP contains statements that must either all succeed or all fail and if we know for sure that this SP will always be called from inside the transaction, then there is never a need to start a new transaction inside this SP ( assuming we dont want to use save points). but if were not sure whether our SP will be called from within some transaction, then we should always first check if SP is called from within the running transaction, and only if its not running within any transaction, should we start a new transaction within SP? > Nevertheless, those articles can give you some idea of what's going on. Will check it out, thanx
From: Erland Sommarskog on 10 May 2010 17:55 trubar a (asponmynet(a)gmail.com) writes: > On May 8, 10:24�am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: >> But note that the check is on @@trancount - so, if A starts a >> transaction, and then calls B which performs COMMIT TRANSACTION >> followed by BEGIN TRANSACTION, there will be no error raised on exist. > > I assume that too is considered a bad programming practice? Definitely. > I�m not quite sure what you mean by batch and transaction being > aborted directly?! For example: > > * if I run the following SP without the TRY-CATCH and if the DELETE > causes an error, then statements ( in our example INSERT statement ) > following DELETE statement will still get executed: Maybe. As I discuss in my articles, different errors have different effects. Consider these two bcatches: select convert(datetime, '20080230') print 'This prints' go select convert(datetime, '2080230') print 'This does not print' go In the latter case, if there was an active transaction, it would be rolled back. And please don't ask my there different errors yields different effect. It's just a big mess! > � if we know for sure that this SP will always be called from inside > the transaction, then there is never a need to start a new transaction > inside this SP ( assuming we don�t want to use save points). For such procedure, I usually include: IF @@trancount = 0 RAISERROR('This procedure must be called from within a transaction', 16, 1) This is particularly important if the procedure is known to perform only part of a transaction, and executing it on its own would lead to violation of business rules. > � but if we�re not sure whether our SP will be called from within some > transaction, then we should always first check if SP is called from > within the running transaction, and only if it�s not running within > any transaction, should we start a new transaction within SP? Hm, in theory. In practice I say: always start a transaction, unless there is a really compelling reason to play with savepoints. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: trubar a on 11 May 2010 14:28
On May 10, 2:55 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > trubar a (asponmy...(a)gmail.com) writes: > > Im not quite sure what you mean by batch and transaction being > > aborted directly?! For example: > > > * if I run the following SP without the TRY-CATCH and if the DELETE > > causes an error, then statements ( in our example INSERT statement ) > > following DELETE statement will still get executed: > > Maybe. As I discuss in my articles, different errors have different > effects. Consider these two bcatches: > > select convert(datetime, '20080230') > print 'This prints' > go > select convert(datetime, '2080230') > print 'This does not print' > go > Ok, this is confusing. Both batches produce the same type of error, but print statement in first bacth gets executed, while in second batch doesnt. > In the latter case, if there was an active transaction, it would be > rolled back. > a) By latter case are you referring to second batch? Thus, if both batches were each in its own transaction, then error in the first batch wouldnt cause a rollback, while error in second batch would cause a rollback? b) Perhaps a stupid question, but when you say error in second batch would cause a rollback, are you implying that rollback would happen even if we didnt include a ROLLBACK statement inside the transaction: begin transaction select convert(datetime, '2080230') print 'This does not print' commit go > > > but if were not sure whether our SP will be called from within some > > transaction, then we should always first check if SP is called from > > within the running transaction, and only if its not running within > > any transaction, should we start a new transaction within SP? > > Hm, in theory. In practice I say: always start a transaction, unless > there is a really compelling reason to play with savepoints. > a) You lost me here. Assuming SP checks whether it is called within a running transaction and assuming check confirms that it was called from within some transaction, then why would it be a good idea to start another transaction inside this SP? b) Also, you are stating that we essentially have two choices: either starting a new transaction inside SP ( even if SP was called from within the transaction ) or using save points. But at least to my understanding, I dont see why save points would less needed ( or not needed at all ) of if SP called from within a transaction also starts its own transaction?! In both cases we would need savepoint to rollback just the statements executed inside this SP, so uh, Im dumb |