Sql Transaction

Hi
UPDATE rsSalesRecords
SET SalesReansactionDate=NULL
Where SalesPersonID='CCR15200'

COMMIT TRANSACTION;


I need to know if i used Commit Transaction,What will happen

What is the best way to execute above sql with or with out COMMIT TRANSACTION;
LVL 10
ukerandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
SQL Server uses auto-commits. Thus when you don't explicitly start a transaction with BEGIN TRANSACTION; you don't need a  COMMIT|ROLLBACK TRANSACTION;, cause it is automatically committed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
it throw error message if you didn't start the transaction using "BEGIN TRANSACTION"
0
Deepak ChauhanSQL Server DBACommented:
If you want to use COMMIT TRAN then you have to start your transaction with BEGIN Tran

Begin Transaction   --- or Begin tran
UPDATE rsSalesRecords
SET SalesReansactionDate=NULL
Where SalesPersonID='CCR15200'
COMMIT TRANSACTION; -- or Commit - or Commit Tran

Otherwise without Begin tran sql server treat this as an Implicit transaction which is auto committed.

According to your code:  You did not Begin the tran with "Begin Tran" so in this case SQL server update the record and because it will be treat a implicit transaction but when it will reach to the the next line "commit Transaction" , an error will be raise saying "

"Msg 3902, Level 16, State 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. "


UPDATE rsSalesRecords
SET SalesReansactionDate=NULL
Where SalesPersonID='CCR15200'
COMMIT TRANSACTION;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ukerandiAuthor Commented:
after that its unlock the filed or i have write to sql to unlock the fields/Table
0
Deepak ChauhanSQL Server DBACommented:
this will unlock the filed if not started with Begin Transaction.
0
Deepak ChauhanSQL Server DBACommented:
Begin Transaction   --- or Begin tran
UPDATE rsSalesRecords
SET SalesReansactionDate=NULL
Where SalesPersonID='CCR15200'   ************* "Lock acquired"

COMMIT TRANSACTION; -- or Commit - or Commit Tran *********** " lock released"

========================================================================================
UPDATE rsSalesRecords
SET SalesReansactionDate=NULL
Where SalesPersonID='CCR15200'   ************* "Lock acquired and automatically released by SQL server"

No need to COMMIT here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.