Showing posts with label operations. Show all posts
Showing posts with label operations. Show all posts

Thursday, March 22, 2012

Atomic Transaction

Hi all,

If I have 2 database operations
1. Update ...
2. Delete ...

I want these 2 operations to execute atomically, thus I make use of

BEGIN Transaction ...
Update ...
Delete ...
COMMIT Transaction ...

But if one of these operation fails, it locks up the database. Does anyone have a resolution for this? How do I detect sql Exceptions to release the transaction so that it doesn't lock up the database?

Thanx

James :)I assume this is written in a stored procedure.

In that case

After your first UPDATE you need to chek

@.@.ERROR

If its Zero continue otherwise you need to ROLLBACK the transaction as the update failed. The same is true for your Delete statement. You should only COMMIT when both succeed. You should report or log the error somehow of course.

Have a look in Books Online under the topic

Using @.@.ERROR

its under Accessing and Changing relational Data :: Advanced Query Concepts :: Error Handling

regards

Steve|||Thanx, I will give that a try.
I love dbforums, people here's always got the answer.

James :D

Asynchronous / non-blocking trigger

Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
Jon
Triggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>
|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>
|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>

Asynchronous / non-blocking trigger

Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
JonTriggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway. :)
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>> Is it possible to create an asynchronous / non-blocking trigger in SQL
>> Server 2005 that will allow operations to continue asynchronously while
>> the trigger can still read the in-memory "inserted" and "deleted" virtual
>> tables?
>> Thanks,
>> Jon
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway. :)
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>> Triggers always execute synchronously in the context of a transaction.
>> If you need to invoke an asynchronous process from within a trigger,
>> consider using Service Broker. Asynchronous triggers are exactly the
>> functionality that Service Broker provides. See the Books Online for
>> details.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
>> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>> Is it possible to create an asynchronous / non-blocking trigger in SQL
>> Server 2005 that will allow operations to continue asynchronously while
>> the trigger can still read the in-memory "inserted" and "deleted"
>> virtual tables?
>> Thanks,
>> Jon
>>
>

Asynchronous / non-blocking trigger

Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
JonTriggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/s...br />
ers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>