February 16, 2011 at 3:44 am
Hi,
if i don't specify commit then when delete command will be commited?
February 16, 2011 at 4:34 am
Assuming a standard stored procedure, when the procedure reaches it's end. With a query outside of a procedure, when the batch completes (there's a go statement or it's the end of the statements). That's assuming you didn't put a BEGIN TRAN in there. If you do that and don't put a COMMIT TRAN, then that transaction is still waiting, uncommitted. Every query has an implicit transaction wrapping it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2011 at 4:57 am
abhas (2/16/2011)
Hi,if i don't specify commit then when delete command will be commited?
The moment that the delete command completes (assuming no explicit begin tran and that implicit transactions is off)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 6:55 am
Grant Fritchey (2/16/2011)
Assuming a standard stored procedure, when the procedure reaches it's end. With a query outside of a procedure, when the batch completes (there's a go statement or it's the end of the statements
Surely that's when the delete stamement in the procedure finishes, not the whole procedure?
Inside a batch it's as soon as the delete statement has finished, not the whole batch?
February 16, 2011 at 7:02 am
Ian Scarlett (2/16/2011)
Grant Fritchey (2/16/2011)
Assuming a standard stored procedure, when the procedure reaches it's end. With a query outside of a procedure, when the batch completes (there's a go statement or it's the end of the statementsSurely that's when the delete stamement in the procedure finishes, not the whole procedure?
Inside a batch it's as soon as the delete statement has finished, not the whole batch?
I post something stupid at least once a day, but I think I'm right on this one if implicit transactions are on. But, like I said, this could be my first stupid post today.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2011 at 7:07 am
If implicit transactions are on, the transaction begins when the first statement/modification occurs (I'm not sure which) and commits when an explicit COMMIT TRANSACTION statement runs. All that implicit transactions give you is the ability to omit BEGIN TRAN. Anything other than autocommit (the default) requires an explicit COMMIT TRANSACTION.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 7:13 am
GilaMonster (2/16/2011)
If implicit transactions are on, the transaction begins when the first statement/modification occurs (I'm not sure which) and commits when an explicit COMMIT TRANSACTION statement runs. All that implicit transactions give you is the ability to omit BEGIN TRAN. Anything other than autocommit (the default) requires an explicit COMMIT TRANSACTION.
USE master
SET IMPLICIT_TRANSACTIONS ON
SELECT * FROM sys.objects
--this returns no error, so even a simple select starts a transaction
COMMIT
SET IMPLICIT_TRANSACTIONS OFF
This is very usefull when coding a delete statement. I run with implicit transaction on all the time since january and it's quite usefull (just have to remember to commit them so you don't deadlock yourself. I personnally preffer to write the ROLLBACK statement first before doing anything else.
So I can't possibly commit anything by accident.
I force this by manually typing commit when it's time, exec that line of code then delete it right away.
I don't know why, but his year I've had to do a few live updates in the system and this is the safest way I've found to do it (yes I do take backups but that's a much bigger hassle when you need to restore something).
February 16, 2011 at 7:14 am
Grant Fritchey (2/16/2011)
but I think I'm right on this one if implicit transactions are on.
I also thought that if you executed a procedure on a connection with implicit transactions on, the changes would still not be committed once the procedure has finished executing, but would wait until you do a commit.
February 16, 2011 at 7:16 am
Ian Scarlett (2/16/2011)
Grant Fritchey (2/16/2011)
but I think I'm right on this one if implicit transactions are on.I also thought that if you executed a procedure on a connection with implicit transactions on, the changes would still not be committed once the procedure has finished executing, but would wait until you do a commit.
That's exactly how it goes!
Annoying at first... untill it saves your arse! :w00t:
February 16, 2011 at 7:18 am
Ian Scarlett (2/16/2011)
Grant Fritchey (2/16/2011)
but I think I'm right on this one if implicit transactions are on.I also thought that if you executed a procedure on a connection with implicit transactions on, the changes would still not be committed once the procedure has finished executing, but would wait until you do a commit.
Yup, that's how it is. If the connection is closed and a commit has not run then the transaction is rolled back.
Beware mixing implicit and explicit transactions. Too easy to commit one time too few.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 7:19 am
GilaMonster (2/16/2011)
Ian Scarlett (2/16/2011)
Grant Fritchey (2/16/2011)
but I think I'm right on this one if implicit transactions are on.I also thought that if you executed a procedure on a connection with implicit transactions on, the changes would still not be committed once the procedure has finished executing, but would wait until you do a commit.
Yup, that's how it is. If the connection is closed and a commit has not run then the transaction is rolled back.
Beware mixing implicit and explicit transactions. Too easy to commit one time too few.
Ah, so it is my first stupid statement of the day. Cool. Retracted. Apologies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2011 at 7:29 am
GilaMonster (2/16/2011)
Beware mixing implicit and explicit transactions. Too easy to commit one time too few
Been there, done that, got the t-shirt.
Went to lunch whilst one commit short of a transaction :blush:
February 16, 2011 at 9:47 am
Hi,
Thanks of your valuable comments
Regards
Abhas
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply