|
Home > Archive > MS SQL Server > February 2006 > is it better to use Begin-Commit?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
is it better to use Begin-Commit?
|
|
|
| Hi All,
is it better to use Begin...Commit in all SP?
does this help preventing deadlocks?
thanks in advance.
jouj
| |
| Tibor Karaszi 2006-02-28, 8:23 pm |
| It is not that simple. Serializing the access though some objects can in some cases reduce the
likelihood for deadlocks. But it can also have the opposite effect, as locks will be held for a
longer time increasing the risk that two or more operations executes partly concurrently. Also, you
need proper error handling, and not just add begin tran and commit tran, or you will not have the
desired logic. Analyze the requirements for your modifications and code your transactions according
to that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"jouj" <jouj@discussions.microsoft.com> wrote in message
news:D0FB6737-8D16-4BF3-9FD9- E640AF51EAC8@microso
ft.com...
> Hi All,
> is it better to use Begin...Commit in all SP?
> does this help preventing deadlocks?
>
> thanks in advance.
> jouj
>
| |
| bluefish 2006-02-28, 8:23 pm |
| Jouj,
Using this, your entire transaction will not be committed until you use
commit tran. It is good to use is sp in error trapping, since if an error
occurred somewhere, you can jump to an Error section and use 'rollback
transactions' command to undo the entire set of transactions.
I have noticed that after you open the transactions, (from begin tran) to
till you close the transactions (Commit or Rollback), update tables are
locked for modifications.
We use this in transactions than need to happen in all or nothing manner.
|
|
|
|
|