1 Replies - 573 Views - Last Post: 16 January 2009 - 03:25 AM Rate Topic: -----

#1 George2  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 83
  • Joined: 14-December 08

2 basic transaction issues

Posted 16 January 2009 - 12:26 AM

Hello everyone,

Two basic questions about transactions in SQL Server 2005,

1. For single SQL insert/update/delete statement, are there any needs or benefits to wrap into a transaction?

2. I wrote T-SQL like this,

BEGIN TRY
Begin Transaction
...
commit
END TRY
BEGIN CATCH
rollback
...
END CATCH



my question is, is it a must to call commit explicitly just before end try statement? Any means to commit transaction automatically after end try statement?

regards,
George

Is This A Good Question/Topic? 0
  • +

Replies To: 2 basic transaction issues

#2 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 24
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: 2 basic transaction issues

Posted 16 January 2009 - 03:25 AM

1) You only want to use a transaction if you have multiple statements that you want to execute together.

For example: When a row in table 1 is updated a row in table 2 is deleted - if this must happen together then wrap both statements in a transaction.

2)You need to explicitly call COMMIT TRANS because the transaction holds locks on the tables, or rows (so other statements executed at the same time cannot access them) and the COMMIT (or ROLLBACK) statement will release them [the locks].

This post has been edited by Footsie: 16 January 2009 - 03:26 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1