SQL Server Output Buffer
in [Server Configuration]
|
From: Jeroen Mostert on 6 Dec 2009 19:29 Jay wrote: > I made the point on batch vs. transaction because I was helping someone fix > a routine a while back and their problem was that, because a batch groups > statements, like a transaction, they were treating a batch as if it was a > transaction. This was in 2000. Closely related surprisingly common mistake: people thinking that the entire body of a stored procedure always executes in a transaction. Being used to the concept of a subroutine from programming languages, they assume that stored procedures represent some form of indivisible unit of work, hence an implicit transaction. No such luck. If you want batches to be treated as transactions, there's an option for that: IMPLICIT_TRANSACTIONS. Turn it on and SQL Server behaves like Oracle does by default: batches begin a transaction that needs to be explicitly committed or rolled back. It's usually off, meaning that individual statements are committed immediately. Setting this option to one value for code that expects it to be the other is not recommended. :-) -- J.
From: Jay on 6 Dec 2009 21:14 Haven't run into someone thinking a proc was a transaction, but can see it easily. The IMPLICIT_TRANSACTIONS is news to me though. That kind of fly's in the face of Erland saying batches have nothing to do with transactions. I don't think I like it though, it hides something in relational databases that really should be understood. "Jeroen Mostert" <jmostert(a)xs4all.nl> wrote in message news:4b1c4c67$0$22903$e4fe514c(a)news.xs4all.nl... > Jay wrote: >> I made the point on batch vs. transaction because I was helping someone >> fix a routine a while back and their problem was that, because a batch >> groups statements, like a transaction, they were treating a batch as if >> it was a transaction. This was in 2000. > > Closely related surprisingly common mistake: people thinking that the > entire body of a stored procedure always executes in a transaction. Being > used to the concept of a subroutine from programming languages, they > assume that stored procedures represent some form of indivisible unit of > work, hence an implicit transaction. No such luck. > > If you want batches to be treated as transactions, there's an option for > that: IMPLICIT_TRANSACTIONS. Turn it on and SQL Server behaves like Oracle > does by default: batches begin a transaction that needs to be explicitly > committed or rolled back. It's usually off, meaning that individual > statements are committed immediately. Setting this option to one value for > code that expects it to be the other is not recommended. :-) > > -- > J.
From: Kalen Delaney on 7 Dec 2009 00:58 I would not say that using IMPLICIT_TRANSACTIONS changes anything about what Erland said, and I would avoid using this option. In fact, when you use the IMPLICIT_TRANSACTIONS option, it does not mean that every batch begins a transaction, or that batches are treated as transactions. Jeroen was greatly oversimplifying. You can read the details in the documentation. Whether you have IMPLICIT_TRANSACTIONS ON or OFF, there is a many to many relationship between batches and transactions... I.e. one transaction can span many batches, and one batch can contain many transactions. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Jay" <spam(a)nospam.org> wrote in message news:eaD9PMudKHA.5136(a)TK2MSFTNGP02.phx.gbl... > Haven't run into someone thinking a proc was a transaction, but can see it > easily. > > The IMPLICIT_TRANSACTIONS is news to me though. That kind of fly's in the > face of Erland saying batches have nothing to do with transactions. I > don't think I like it though, it hides something in relational databases > that really should be understood. > > > "Jeroen Mostert" <jmostert(a)xs4all.nl> wrote in message > news:4b1c4c67$0$22903$e4fe514c(a)news.xs4all.nl... >> Jay wrote: >>> I made the point on batch vs. transaction because I was helping someone >>> fix a routine a while back and their problem was that, because a batch >>> groups statements, like a transaction, they were treating a batch as if >>> it was a transaction. This was in 2000. >> >> Closely related surprisingly common mistake: people thinking that the >> entire body of a stored procedure always executes in a transaction. Being >> used to the concept of a subroutine from programming languages, they >> assume that stored procedures represent some form of indivisible unit of >> work, hence an implicit transaction. No such luck. >> >> If you want batches to be treated as transactions, there's an option for >> that: IMPLICIT_TRANSACTIONS. Turn it on and SQL Server behaves like >> Oracle does by default: batches begin a transaction that needs to be >> explicitly committed or rolled back. It's usually off, meaning that >> individual statements are committed immediately. Setting this option to >> one value for code that expects it to be the other is not recommended. >> :-) >> >> -- >> J. > >
From: Erland Sommarskog on 7 Dec 2009 03:20 Jay (spam(a)nospam.org) writes: > The IMPLICIT_TRANSACTIONS is news to me though. That kind of fly's in > the face of Erland saying batches have nothing to do with transactions. > I don't think I like it though, it hides something in relational > databases that really should be understood. As Kalen said, there is still no correspondence between batches and implict transactions. What implicit transaction means is that as soon there is a statement that changes the database, there is an implicit BEGIN TRANSACTION before it. There is never an implicit COMMIT TRANSACTION. Or you could describe it this way, no matter the setting is on or off, there is always a BEGIN TRANSACTION before something that changes something. But when the setting is off, there is always an implicit COMMIT at the end. Or an implicit ROLLBACK in case of failure. One problem with implicit transactions in SQL Server is that since this is an "unusual" features, and there may situations where you don't get an implicit transaction when you should. I know that I found one such bug with INSERT EXEC in SQL 2000, now corrected. However, there are some APIs that set implicit transactions under some circumstances. I believe I've seen this with OPENQUERY, for instance. (On the remote server, that is.) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
First
|
Prev
|
Pages: 1 2 3 4 Prev: Collation settings Next: Sql Server Indexing With Two or More Columns |