Professional Web Applications Themes

Transactions - ADO/SQL or both? - Microsoft SQL / MS SQL Server

Hi, When considering transactions should you: 1) - Put a begin and end tran inside every SP that alters data? 2) - Use the ADO Transaction features 3) - Mix 1 and 2 4) - Do something different I appreciate it depends on the exact situation, but, as a general guide for a small system (30 users, low numbers of updates an hour e.t.c) what's the guide? Thanks...

  1. #1

    Default Transactions - ADO/SQL or both?

    Hi,

    When considering transactions should you:

    1) - Put a begin and end tran inside every SP that alters data?
    2) - Use the ADO Transaction features
    3) - Mix 1 and 2
    4) - Do something different

    I appreciate it depends on the exact situation, but, as a general guide for
    a small system (30 users, low numbers of updates an hour e.t.c) what's the
    guide?

    Thanks


    London Developer Guest

  2. #2

    Default Re: Transactions - ADO/SQL or both?


    "London Developer" <devnowhere.com> wrote in message
    news:OeL6QrKRDHA.3192tk2msftngp13.phx.gbl...
    > Hi,
    >
    > When considering transactions should you:
    >
    > 1) - Put a begin and end tran inside every SP that alters data?
    > 2) - Use the ADO Transaction features
    > 3) - Mix 1 and 2
    > 4) - Do something different
    >
    > I appreciate it depends on the exact situation, but, as a general guide
    for
    > a small system (30 users, low numbers of updates an hour e.t.c) what's the
    > guide?
    There is really no difference. ADO just issues a BEGIN TRANSACTION under the
    covers.

    Use the following pattern for stored procedures which issue multiple DML
    statements

    create procedure sp_foo
    as
    begin transaction --possibly "nested"
    save transaction sp_foo

    insert into foo(i) values(1)
    if error <> goto eh

    insert into foo(i) values(2)
    if error <> goto eh

    commit transaction --possibly "nested"
    return 0

    eh:
    rollback transaction sp_foo
    commit transaction --now empty
    return 1



    Such a stored procedure will be atomic, and will work correctly whether or
    not the client has already begun a transaction. Then you can freely
    intermingle "ADO" transactions and "SQL" transactions.

    In any case there is no reason to put any transaction control inside a
    stored procedure which only contains a single DML statement, as single DML
    statements are always atomic.


    David



    David Browne Guest

  3. #3

    Default Re: Transactions - ADO/SQL or both?

    Thanks for the replys, all very helpful.

    Just to clarify a "single DML statement" - are you implying simply a single
    unpdate / insert?

    So, if I had multiple updates e.t.c within an SP then would these would act
    slightly differently?

    For example, if there were two updates and I needed them both to fail or
    complete, then would I need to implement my own transaction within an SP or
    does the fact that they're in an SP mean they both fail or complete
    together?

    "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in
    message news:eLUV1gLRDHA.2320TK2MSFTNGP12.phx.gbl...
    >
    > "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in
    > message news:%23caHUwKRDHA.3796tk2msftngp13.phx.gbl...
    > >
    > > "London Developer" <devnowhere.com> wrote in message
    > > news:OeL6QrKRDHA.3192tk2msftngp13.phx.gbl...
    > > > Hi,
    > > >
    > > > When considering transactions should you:
    > > >
    > > > 1) - Put a begin and end tran inside every SP that alters data?
    > > > 2) - Use the ADO Transaction features
    > > > 3) - Mix 1 and 2
    > > > 4) - Do something different
    > > >
    > > > I appreciate it depends on the exact situation, but, as a general
    guide
    > > for
    > > > a small system (30 users, low numbers of updates an hour e.t.c) what's
    > the
    > > > guide?
    > >
    > > There is really no difference. ADO just issues a BEGIN TRANSACTION under
    > the
    > > covers.
    > >
    > > Use the following pattern for stored procedures which issue multiple DML
    > > statements
    > >
    > > create procedure sp_foo
    > > as
    > > begin transaction --possibly "nested"
    > > save transaction sp_foo
    > >
    > > insert into foo(i) values(1)
    > > if error <> goto eh
    > >
    > > insert into foo(i) values(2)
    > > if error <> goto eh
    > >
    > > commit transaction --possibly "nested"
    > > return 0
    > >
    > > eh:
    > > rollback transaction sp_foo
    > > commit transaction --now empty
    > > return 1
    > >
    > >
    > >
    > > Such a stored procedure will be atomic, and will work correctly whether
    or
    > > not the client has already begun a transaction. Then you can freely
    > > intermingle "ADO" transactions and "SQL" transactions.
    > >
    > > In any case there is no reason to put any transaction control inside a
    > > stored procedure which only contains a single DML statement, as single
    DML
    > > statements are always atomic.
    > >
    > >
    > > David
    > >
    > Modulo typo's of course
    >
    > create procedure sp_foo
    > as
    > begin transaction --possibly "nested"
    > save transaction sp_foo
    >
    > insert into foo(i) values(1)
    > if error <> 0 goto eh
    >
    > insert into foo(i) values(2)
    > if error <> 0 goto eh
    >
    > commit transaction --possibly "nested"
    > return 0
    >
    > eh:
    > rollback transaction sp_foo
    > commit transaction --now empty
    > return 1
    >
    >
    >

    London Developer Guest

  4. #4

    Default Re: Transactions - ADO/SQL or both?

    > For example, if there were two updates and I needed them both to fail or
    > complete, then would I need to implement my own transaction within an SP or
    > does the fact that they're in an SP mean they both fail or complete
    > together?
    The fact that you have several modifications inside a stored procedure does not make them go
    within a single transaction. You need transaction code handling.
    If you have only one insert or update or delete, then you don't need transaction handling code.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "London Developer" <devnowhere.com> wrote in message
    news:Ox2uhsSRDHA.4024tk2msftngp13.phx.gbl...
    > Thanks for the replys, all very helpful.
    >
    > Just to clarify a "single DML statement" - are you implying simply a single
    > unpdate / insert?
    >
    > So, if I had multiple updates e.t.c within an SP then would these would act
    > slightly differently?
    >
    > For example, if there were two updates and I needed them both to fail or
    > complete, then would I need to implement my own transaction within an SP or
    > does the fact that they're in an SP mean they both fail or complete
    > together?
    >
    > "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in
    > message news:eLUV1gLRDHA.2320TK2MSFTNGP12.phx.gbl...
    > >
    > > "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in
    > > message news:%23caHUwKRDHA.3796tk2msftngp13.phx.gbl...
    > > >
    > > > "London Developer" <devnowhere.com> wrote in message
    > > > news:OeL6QrKRDHA.3192tk2msftngp13.phx.gbl...
    > > > > Hi,
    > > > >
    > > > > When considering transactions should you:
    > > > >
    > > > > 1) - Put a begin and end tran inside every SP that alters data?
    > > > > 2) - Use the ADO Transaction features
    > > > > 3) - Mix 1 and 2
    > > > > 4) - Do something different
    > > > >
    > > > > I appreciate it depends on the exact situation, but, as a general
    > guide
    > > > for
    > > > > a small system (30 users, low numbers of updates an hour e.t.c) what's
    > > the
    > > > > guide?
    > > >
    > > > There is really no difference. ADO just issues a BEGIN TRANSACTION under
    > > the
    > > > covers.
    > > >
    > > > Use the following pattern for stored procedures which issue multiple DML
    > > > statements
    > > >
    > > > create procedure sp_foo
    > > > as
    > > > begin transaction --possibly "nested"
    > > > save transaction sp_foo
    > > >
    > > > insert into foo(i) values(1)
    > > > if error <> goto eh
    > > >
    > > > insert into foo(i) values(2)
    > > > if error <> goto eh
    > > >
    > > > commit transaction --possibly "nested"
    > > > return 0
    > > >
    > > > eh:
    > > > rollback transaction sp_foo
    > > > commit transaction --now empty
    > > > return 1
    > > >
    > > >
    > > >
    > > > Such a stored procedure will be atomic, and will work correctly whether
    > or
    > > > not the client has already begun a transaction. Then you can freely
    > > > intermingle "ADO" transactions and "SQL" transactions.
    > > >
    > > > In any case there is no reason to put any transaction control inside a
    > > > stored procedure which only contains a single DML statement, as single
    > DML
    > > > statements are always atomic.
    > > >
    > > >
    > > > David
    > > >
    > > Modulo typo's of course
    > >
    > > create procedure sp_foo
    > > as
    > > begin transaction --possibly "nested"
    > > save transaction sp_foo
    > >
    > > insert into foo(i) values(1)
    > > if error <> 0 goto eh
    > >
    > > insert into foo(i) values(2)
    > > if error <> 0 goto eh
    > >
    > > commit transaction --possibly "nested"
    > > return 0
    > >
    > > eh:
    > > rollback transaction sp_foo
    > > commit transaction --now empty
    > > return 1
    > >
    > >
    > >
    >
    >

    Tibor Karaszi Guest

Similar Threads

  1. Transactions
    By Eric in forum ASP Components
    Replies: 0
    Last Post: February 20th, 02:42 PM
  2. Transactions and Locking
    By gbrownuk in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 6th, 10:05 PM
  3. Transactions in Access
    By Astra in forum ASP Database
    Replies: 2
    Last Post: February 18th, 02:01 PM
  4. Transactions....HELP!
    By John Berry in forum Informix
    Replies: 0
    Last Post: October 16th, 06:54 PM
  5. [PHP] HTTP transactions with PHP?
    By Matt Matijevich in forum PHP Development
    Replies: 1
    Last Post: August 21st, 08:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139