Hi I am a DBA and am having a dispute with a developer. He insists on
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
KristinaKristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> > Kristina
> >
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegroups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>> KristinaDBA@.gmail.com wrote:
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> >
>> > Begin train
>> >
>> > 1 insert statment
>> >
>> > Check for error
>> >
>> > Commit or rollback.
>> >
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.
>> >
>> > Thanks in advance.
>> > Kristina
>> >
>> I'd have to side with your developer on this one... You're "assuming"
>> that SQL will take care of the error handling for you. He's
>> GUARANTEEING that the errors will be handled in an expected fashion.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>
>
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> > Begin train
> > 1 insert statment
> > Check for error
> > Commit or rollback.
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.For a single DML statement, there is no need, but no real cost, to wrapping
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedure
> or in the client code.
> David- Hide quoted text -- Show quoted text -|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>>
>>
>> > Hi I am a DBA and am having a dispute with a developer. He insists on
>> > coding like this:
>> > Begin train
>> > 1 insert statment
>> > Check for error
>> > Commit or rollback.
>> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
>> > is to NOT put them in unless they are needed and at least 2 statements
>> > occur (insert, update, delete). I am assuming that these extra begin
>> > trans affect performance and the log some how. Can anyone help me with
>> > this. Again, this is the case when there is 1 update, delete, or
>> > insert.For a single DML statement, there is no need, but no real cost,
>> > to wrapping
>> it in an explicit transaction. It might generate an extra log record or
>> two, but nothing to worry about.
>> But, what if you want to enlist this procedure in a larger transaction?
>> I
>> am generally against explicit transaction handling in stored procedures.
>> While it's sometimes necessary and beneficial It's usually the wrong
>> scope
>> to knit together transactions and decide their fate. You only really
>> want
>> one level of transaction handling, either in the outermost stored
>> procedure
>> or in the client code.
>> David- Hide quoted text -- Show quoted text -
>|||Kalen,
I did read your reply. Can you give me a transact sql example of a
statement that won't roll back - like you mentioned a constraint
violation. I am not aware of this and would be happy to learn..
Thanks.
On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
> --
> HTH
> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
> <Kristina...@.gmail.com> wrote in messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>
> > Kalen,
> > I misspoke. What I mean to say is if there was an error, they would be
> > automically rolled back. - no need for begin trans. In the case of no
> > errors with a single statment, they would be automatically committed.
> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> > m...@.hotmail.com> wrote:
> >> <Kristina...@.gmail.com> wrote in
> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
> >> > Hi I am a DBA and am having a dispute with a developer. He insists on
> >> > coding like this:
> >> > Begin train
> >> > 1 insert statment
> >> > Check for error
> >> > Commit or rollback.
> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> >> > is to NOT put them in unless they are needed and at least 2 statements
> >> > occur (insert, update, delete). I am assuming that these extra begin
> >> > trans affect performance and the log some how. Can anyone help me with
> >> > this. Again, this is the case when there is 1 update, delete, or
> >> > insert.For a single DML statement, there is no need, but no real cost,
> >> > to wrapping
> >> it in an explicit transaction. It might generate an extra log record or
> >> two, but nothing to worry about.
> >> But, what if you want to enlist this procedure in a larger transaction?
> >> I
> >> am generally against explicit transaction handling in stored procedures.
> >> While it's sometimes necessary and beneficial It's usually the wrong
> >> scope
> >> to knit together transactions and decide their fate. You only really
> >> want
> >> one level of transaction handling, either in the outermost stored
> >> procedure
> >> or in the client code.
> >> David- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David|||I apologize. I should really finish my coffee in the morning before become
insistent upon anything!
I was thinking of multiple statements where one failed but the rest
succeeded. But that of course would always have to have BEGIN TRAN.
One single statement will have a partial success. It will be all or nothing.
That being said, adding the BEGIN TRAN does not really add any extra
overhead, UNLESS you somehow forget to COMMIT or ROLLBACK and your
transaction ends up staying open for longer than necessary. Then there are
all kinds of ramifications.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165339226.543180.164360@.f1g2000cwa.googlegroups.com...
> Kalen,
> I did read your reply. Can you give me a transact sql example of a
> statement that won't roll back - like you mentioned a constraint
> violation. I am not aware of this and would be happy to learn..
> Thanks.
> On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> I understand that. Did you even read my reply?
>> I said that there are some errors that do NOT automatically rollback,
>> that
>> will commit even with the error. So the only way to roll them back is for
>> YOU or your code to catch them before the commit, and the only way to do
>> that is to have BEGIN TRAN.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVPhttp://sqlblog.com
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.com...
>>
>> > Kalen,
>> > I misspoke. What I mean to say is if there was an error, they would be
>> > automically rolled back. - no need for begin trans. In the case of no
>> > errors with a single statment, they would be automatically committed.
>> > On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
>> > m...@.hotmail.com> wrote:
>> >> <Kristina...@.gmail.com> wrote in
>> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.com...
>> >> > Hi I am a DBA and am having a dispute with a developer. He insists
>> >> > on
>> >> > coding like this:
>> >> > Begin train
>> >> > 1 insert statment
>> >> > Check for error
>> >> > Commit or rollback.
>> >> > Since SQL Server 2000 an d 2005 has implicit transactions, my
>> >> > standard
>> >> > is to NOT put them in unless they are needed and at least 2
>> >> > statements
>> >> > occur (insert, update, delete). I am assuming that these extra begin
>> >> > trans affect performance and the log some how. Can anyone help me
>> >> > with
>> >> > this. Again, this is the case when there is 1 update, delete, or
>> >> > insert.For a single DML statement, there is no need, but no real
>> >> > cost,
>> >> > to wrapping
>> >> it in an explicit transaction. It might generate an extra log record
>> >> or
>> >> two, but nothing to worry about.
>> >> But, what if you want to enlist this procedure in a larger
>> >> transaction?
>> >> I
>> >> am generally against explicit transaction handling in stored
>> >> procedures.
>> >> While it's sometimes necessary and beneficial It's usually the wrong
>> >> scope
>> >> to knit together transactions and decide their fate. You only really
>> >> want
>> >> one level of transaction handling, either in the outermost stored
>> >> procedure
>> >> or in the client code.
>> >> David- Hide quoted text -- Show quoted text -- Hide quoted text --
>> >> Show quoted text -
>|||KristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
Kristina,
I did a quick benchmark:
CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
IF @.@.ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go
I ran them 3 times and I did not notice any significant differences in
neither of 4 counters.
I do not know anything about your environment, but I would not worry
about performance penalties of BEGIN TRAN in mine.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thanks Alex,
That definitely clarifies the issue!
Also, Kalen - glad you are not *mad* any more...
This is a great group, I am going to stick with it. Today was my first
day checking it out :).
Kristina.
Alex Kuznetsov wrote:
> KristinaDBA@.gmail.com wrote:
> > Hi I am a DBA and am having a dispute with a developer. He insists on
> > coding like this:
> >
> > Begin train
> >
> > 1 insert statment
> >
> > Check for error
> >
> > Commit or rollback.
> >
> > Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> > is to NOT put them in unless they are needed and at least 2 statements
> > occur (insert, update, delete). I am assuming that these extra begin
> > trans affect performance and the log some how. Can anyone help me with
> > this. Again, this is the case when there is 1 update, delete, or
> > insert.
> >
> > Thanks in advance.
> Kristina,
> I did a quick benchmark:
> CREATE TABLE aa(i INT)
> go
> CREATE PROCEDURE Test1
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> END
> END
> go
> CREATE PROCEDURE Test2
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> BEGIN TRANSACTION
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> IF @.@.ERROR<>0 BEGIN
> ROLLBACK
> END ELSE BEGIN
> COMMIT
> END
> END
> END
> go
> Test1
> go
> Test1
> /*
> Profiler results:
> CPU:406
> Reads: 10720
> Writes: 104
> Duration: 2406
> */
> go
> Test2
> go
> Test2
> /*
> Profiler results:
> CPU:373
> Reads: 10844
> Writes: 116
> Duration: 2406
> */
> go
> DROP PROCEDURE Test1
> go
> DROP PROCEDURE Test2
> go
> DROP TABLE aa
> go
> I ran them 3 times and I did not notice any significant differences in
> neither of 4 counters.
> I do not know anything about your environment, but I would not worry
> about performance penalties of BEGIN TRAN in mine.
> --
> Alex Kuznetsov
> http://sqlserver-tips.blogspot.com/
> http://sqlserver-puzzles.blogspot.com/
Showing posts with label commit. Show all posts
Showing posts with label commit. Show all posts
Sunday, February 12, 2012
BEGIN TRANSACTION COMMIT TRANSACTION help
I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)
For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')
So table testTable1 has a unique index on it..
Now try to run these statements:
--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION
SELECT * FROM testTable2
..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?in order to roll back a transaction, you have to specifically issue the ROLLBACK command. usually after checking the error status of each operation that matters.|||Huh. That sort of stinks. I had thought an error that occurred within an explicit transaction would automatically rollback the transaction. Guess not...
So, I have modified the test as follows and it works:
DECLARE @.errorHolder int
SELECT @.errorHolder = 0
--DELETE FROM testTable2
BEGIN TRANSACTION aaa
insert into testTable1(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
insert into testTable2(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
IF @.errorHolder > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SELECT * FROM testTable2
Is there an easier way|||Use
SET XACT_ABORT ON
to automatically roll back a transaction if a run-time error is raised in the TSQL statements.|||thanks kaffenils, that's much easier.|||I prefer to try and catch my errors withg error handling and then I have control over my logic
BUT DDL?|||I prefer to try and catch my errors withg error handling and then I have control over my logic
I agree that TRY...CATCH is preferrable in SQL Server 2005 as it gives you more control, but craigmc is using SQL Server 2000. It is time consuming work to use @.@.ERROR if the only purpose is to rollback the transaction. XACT_ABORT could provide a qiuick and dirty solution if handling an error only means rolling back the transaction.|||Time consuming? That's your justification?!
Oh dear...|||Time consuming? That's your justification?!
Oh dear...
Did you even read what I wrote? Have I said not to use TRY...CATCH or @.@.ERROR if you need more control of exceptions?
"Oh Dear" :shocked:
All I said was that if you have multiple DML statements (and you're running on SQL Server 2000) and all you need to do if one of them fails is rollback, then it is easier to SET XACT_ABORT ON than to writing multiple
IF @.@.ERROR<>0 BEGIN ... END or [GOTO abc
On SQL Server 2005 I would always use TRY...CATCH, except for my own simple one-time ad-hoc DMLs. Then XACT_ABORT will do the job good enough.
For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')
So table testTable1 has a unique index on it..
Now try to run these statements:
--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION
SELECT * FROM testTable2
..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?in order to roll back a transaction, you have to specifically issue the ROLLBACK command. usually after checking the error status of each operation that matters.|||Huh. That sort of stinks. I had thought an error that occurred within an explicit transaction would automatically rollback the transaction. Guess not...
So, I have modified the test as follows and it works:
DECLARE @.errorHolder int
SELECT @.errorHolder = 0
--DELETE FROM testTable2
BEGIN TRANSACTION aaa
insert into testTable1(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
insert into testTable2(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
IF @.errorHolder > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SELECT * FROM testTable2
Is there an easier way|||Use
SET XACT_ABORT ON
to automatically roll back a transaction if a run-time error is raised in the TSQL statements.|||thanks kaffenils, that's much easier.|||I prefer to try and catch my errors withg error handling and then I have control over my logic
BUT DDL?|||I prefer to try and catch my errors withg error handling and then I have control over my logic
I agree that TRY...CATCH is preferrable in SQL Server 2005 as it gives you more control, but craigmc is using SQL Server 2000. It is time consuming work to use @.@.ERROR if the only purpose is to rollback the transaction. XACT_ABORT could provide a qiuick and dirty solution if handling an error only means rolling back the transaction.|||Time consuming? That's your justification?!
Oh dear...|||Time consuming? That's your justification?!
Oh dear...
Did you even read what I wrote? Have I said not to use TRY...CATCH or @.@.ERROR if you need more control of exceptions?
"Oh Dear" :shocked:
All I said was that if you have multiple DML statements (and you're running on SQL Server 2000) and all you need to do if one of them fails is rollback, then it is easier to SET XACT_ABORT ON than to writing multiple
IF @.@.ERROR<>0 BEGIN ... END or [GOTO abc
On SQL Server 2005 I would always use TRY...CATCH, except for my own simple one-time ad-hoc DMLs. Then XACT_ABORT will do the job good enough.
Begin tran/commit
Hi,
Is it good to have two transaction i.e two begin/commit in one SP.
like
beging
update..
commit
begin
update...
commit.
any performance improvements in any case or otherwise..If those two updates can be commited or rolled back independently from one
another, then your solution is OK.
But if they should be treated as a single atomic unit of work, you should
either enclose them into a single transaction (be it each in its own nested
trasaction or not), and make sure they are committed (or rolled back) as a
single unit of work (i.e. if one needs to be rolled back, the other either
never begins or is rolled back).
Other than that, based on your post, I see no problem. Unless this is your
entire procedure, in which case I see a gazzillion of them. :)
ML|||hi Sunny
This depends on your requirement. You can have 2 transactions in a SP.
If u have 2 different sets of queries, u can group them into two different
queries
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sunny" wrote:
> Hi,
> Is it good to have two transaction i.e two begin/commit in one SP.
> like
> beging
> update..
> commit
> begin
> update...
> commit.
> any performance improvements in any case or otherwise..
Is it good to have two transaction i.e two begin/commit in one SP.
like
beging
update..
commit
begin
update...
commit.
any performance improvements in any case or otherwise..If those two updates can be commited or rolled back independently from one
another, then your solution is OK.
But if they should be treated as a single atomic unit of work, you should
either enclose them into a single transaction (be it each in its own nested
trasaction or not), and make sure they are committed (or rolled back) as a
single unit of work (i.e. if one needs to be rolled back, the other either
never begins or is rolled back).
Other than that, based on your post, I see no problem. Unless this is your
entire procedure, in which case I see a gazzillion of them. :)
ML|||hi Sunny
This depends on your requirement. You can have 2 transactions in a SP.
If u have 2 different sets of queries, u can group them into two different
queries
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sunny" wrote:
> Hi,
> Is it good to have two transaction i.e two begin/commit in one SP.
> like
> beging
> update..
> commit
> begin
> update...
> commit.
> any performance improvements in any case or otherwise..
Begin Tran / Commit Tran
Any reason why you would need to wrap a transaction around a single update?
BEGIN TRANSACTION
UPDATE ThisTable
SET blah blah blah
WHERE foo = @.bar
COMMIT TRANSACTION
?!?
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldNo need. The only reason I can think of if you were to name the transaction.
However, naming
transaction has little benefit.
I think you can see the transaction names as they are rolled forward and rol
led back during
automatic recovery in the errorlog (at least you used to be able to do that)
. That is about the only
reason I can think of. Without a name = waste of precious keyboard keys.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx
.gbl...
> Any reason why you would need to wrap a transaction around a single update
?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> No need. The only reason I can think of if you were to name the
> transaction. However, naming transaction has little benefit.
> I think you can see the transaction names as they are rolled forward and
> rolled back during automatic recovery in the errorlog (at least you used
> to be able to do that). That is about the only reason I can think of.
> Without a name = waste of precious keyboard keys.
Yeah, ok, that's what I thought. Thanks for the much needed "sanity-check".
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||If that's all that's there in the proc, then it's a waste of keystrokes.
Otherwise, if it ain't broke, don't fix it. The real question is, where's
the error handling and the ROLLBACK? someone may have modified another
working procedure that updated multiple tables and just changed the names to
protect the innocent. It won't hurt anything to leave it in, and if you
ever need to add an update to another table, you've already got all of the
error handling and cleanup in place.
You may have a structure something like:
CREATE PROCEDURE procName ...
BEGIN
..
SET @._TRANCOUNT = @.@.TRANCOUNT
IF @._TRANCOUNT > 0
SAVE TRANSACTION transactionName
ELSE
BEGIN TRANSACTION transactionName
..
IF @._TRANCOUNT = 0 -- only commit if new tran started
COMMIT TRANSACTION transactionName
RETURN 0 -- indicate success
ERROR:
IF @._TRANCOUNT IS NOT NULL -- only roll back if necessary
IF @.@.TRANCOUNT > 0 -- roll back any changes made by this proc
ROLLBACK TRANSACTION transactionName
IF @.RC > 0 RETURN @.RC -- return error from called sp
IF @._ERROR > 0 RETURN @._ERROR -- return error raised in this sp
RETURN -1 -- indicate failure
END
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Any reason why you would need to wrap a transaction around a single
update?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
BEGIN TRANSACTION
UPDATE ThisTable
SET blah blah blah
WHERE foo = @.bar
COMMIT TRANSACTION
?!?
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldNo need. The only reason I can think of if you were to name the transaction.
However, naming
transaction has little benefit.
I think you can see the transaction names as they are rolled forward and rol
led back during
automatic recovery in the errorlog (at least you used to be able to do that)
. That is about the only
reason I can think of. Without a name = waste of precious keyboard keys.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx
.gbl...
> Any reason why you would need to wrap a transaction around a single update
?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> No need. The only reason I can think of if you were to name the
> transaction. However, naming transaction has little benefit.
> I think you can see the transaction names as they are rolled forward and
> rolled back during automatic recovery in the errorlog (at least you used
> to be able to do that). That is about the only reason I can think of.
> Without a name = waste of precious keyboard keys.
Yeah, ok, that's what I thought. Thanks for the much needed "sanity-check".
--
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||If that's all that's there in the proc, then it's a waste of keystrokes.
Otherwise, if it ain't broke, don't fix it. The real question is, where's
the error handling and the ROLLBACK? someone may have modified another
working procedure that updated multiple tables and just changed the names to
protect the innocent. It won't hurt anything to leave it in, and if you
ever need to add an update to another table, you've already got all of the
error handling and cleanup in place.
You may have a structure something like:
CREATE PROCEDURE procName ...
BEGIN
..
SET @._TRANCOUNT = @.@.TRANCOUNT
IF @._TRANCOUNT > 0
SAVE TRANSACTION transactionName
ELSE
BEGIN TRANSACTION transactionName
..
IF @._TRANCOUNT = 0 -- only commit if new tran started
COMMIT TRANSACTION transactionName
RETURN 0 -- indicate success
ERROR:
IF @._TRANCOUNT IS NOT NULL -- only roll back if necessary
IF @.@.TRANCOUNT > 0 -- roll back any changes made by this proc
ROLLBACK TRANSACTION transactionName
IF @.RC > 0 RETURN @.RC -- return error from called sp
IF @._ERROR > 0 RETURN @._ERROR -- return error raised in this sp
RETURN -1 -- indicate failure
END
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eNrwSe0pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Any reason why you would need to wrap a transaction around a single
update?
> BEGIN TRANSACTION
> UPDATE ThisTable
> SET blah blah blah
> WHERE foo = @.bar
> COMMIT TRANSACTION
> ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
Begin Tran ...... Commit Tran
What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?
Hi ,
Go through the sql online books.|||
Refer
http://msdn2.microsoft.com/en-us/li...83(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
http://www.mssqlcity.com/Articles/General/TIL.htm|||
simple to roll back the qry process done
Quote:
Originally Posted by sajithamol
What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?
Hi ,
Go through the sql online books.|||
Quote:
Originally Posted by sajithamol
What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?
Refer
http://msdn2.microsoft.com/en-us/li...83(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
http://www.mssqlcity.com/Articles/General/TIL.htm|||
Quote:
Originally Posted by sajithamol
What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?
simple to roll back the qry process done
Friday, February 10, 2012
Begin and Commit Transaction statements in a trigger
I am trying to tidy up a bunch of triggers on a legacy database. The
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
Paddy
Paddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>
|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
Thanks, Liliya
|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
. I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.
|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.
|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go
|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
|||RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
Paddy
Paddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>
|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
Thanks, Liliya
|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
. I did see such a then one time with more then one client.if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.
|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.
|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go
|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
|||RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
Begin and Commit Transaction statements in a trigger
I am trying to tidy up a bunch of triggers on a legacy database. The
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
PaddyPaddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
:). I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
--
Thanks, Liliya|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT|||TEST2
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _trn_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
select * from master.dbo.syslockinfo
exec sp_lock
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exe
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit|||id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000|||----
sp_lock in sp exec @.val= _nval_test call and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
get in in sp DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read uncommitted
(14 row(s) affected)
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||--
sp_lock in sp at the end
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:3 0x0009050098BD01000100030000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:4 0x0009050098BD01000100040000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 5 126623494 0 RID 1:114072:3 X GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 126623494 0 RID 1:114072:4 X GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
NEXTVAL
--
6|||--
sp_lock in sp exit and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 1 85575343 0 TAB IS GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
2 2 _trn_test in sp _nval_test
1 2 _trn_test in sp begin next tran, audit
1 1 _trn_test in sp after SET IMPLICIT_TRANSACTIONS OFF
0 NULL _trn_test begin script,after exe sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
Thanks, Liliya
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
PaddyPaddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
:). I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
--
Thanks, Liliya|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT|||TEST2
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _trn_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
select * from master.dbo.syslockinfo
exec sp_lock
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exe
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit|||id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000|||----
sp_lock in sp exec @.val= _nval_test call and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
get in in sp DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read uncommitted
(14 row(s) affected)
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||--
sp_lock in sp at the end
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:3 0x0009050098BD01000100030000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:4 0x0009050098BD01000100040000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 5 126623494 0 RID 1:114072:3 X GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 126623494 0 RID 1:114072:4 X GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
NEXTVAL
--
6|||--
sp_lock in sp exit and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 1 85575343 0 TAB IS GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
2 2 _trn_test in sp _nval_test
1 2 _trn_test in sp begin next tran, audit
1 1 _trn_test in sp after SET IMPLICIT_TRANSACTIONS OFF
0 NULL _trn_test begin script,after exe sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
Thanks, Liliya
Subscribe to:
Posts (Atom)