Extended Stored Procedure locking up inside transaction 
Author Message
 Extended Stored Procedure locking up inside transaction

Hi everybody

We have an odd setup in place where a trigger is fired due to events
that happened inside of a transaction. This trigger then calls an
extended stored procedure that transposes and exports one of the tables.

The first problem is that the extended stored procedure does select
statements on tables that are involved in the transaction and are
therefore locked. The second problem seems to be that the extended
stored procedure seems to be running in the same thread as the
transaction that triggers it, which leaves me in a deadlock situation
(trasaction not complete until trigger is finished, trigger can't finish
until transaction is done). In this application the triggered event
needs to wait for the transaction, but the transaction shouldn't  need
to wait for the trigger to finish.

I have thought about multi-threading the extended stored procedure but
as I see it, the worker thread will terminate when the extended stored
procedure returns control to the trigger.

Any thoughts?

Thanks

Doug Mewhort
Manager - Development
Synapse Publishing Inc.



Sun, 06 Aug 2000 03:00:00 GMT
 Extended Stored Procedure locking up inside transaction

Not sure if this will help in your situation, but take a look at the
"bound connections" topic in Books Online.  Do not know how stable
this is though.  I tried it once with the original 6.5 version, played
for a couple of hours and decided it was infested with bugs ( or maybe
I just did not realize its limitations).  With SP4 it can be much more
reliable than I thought it was then.

Quote:

>Hi everybody

>We have an odd setup in place where a trigger is fired due to events
>that happened inside of a transaction. This trigger then calls an
>extended stored procedure that transposes and exports one of the tables.

>The first problem is that the extended stored procedure does select
>statements on tables that are involved in the transaction and are
>therefore locked. The second problem seems to be that the extended
>stored procedure seems to be running in the same thread as the
>transaction that triggers it, which leaves me in a deadlock situation
>(trasaction not complete until trigger is finished, trigger can't finish
>until transaction is done). In this application the triggered event
>needs to wait for the transaction, but the transaction shouldn't  need
>to wait for the trigger to finish.

>I have thought about multi-threading the extended stored procedure but
>as I see it, the worker thread will terminate when the extended stored
>procedure returns control to the trigger.

>Any thoughts?

>Thanks

>Doug Mewhort
>Manager - Development
>Synapse Publishing Inc.



Sun, 06 Aug 2000 03:00:00 GMT
 Extended Stored Procedure locking up inside transaction

Just a quick note to let the group know of how we solved this problem. We
are using the trigger to schedule a task that immediately executes the
stored procedure. That effectively creates a new thread inside of SQL Server
and works like a charm.

Thanks to everybody who helped

Doug Mewhort

Manager - Development
Synapse Publishing Inc.

(403) 492-5680



Tue, 08 Aug 2000 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. COM inside an extended stored procedure

2. How to avoid locking for step 2 inside a stored procedure

3. How to exec a stored procedure inside another stored procedure

4. calling a stored procedure inside a stored procedure

5. Stored Procedures vs Extended Stored Procedures

6. Calling extended stored procedure from stored procedure

7. output parameters stored procedures / extended stored procedures

8. Procedure inside stored procedure

9. ADO row locking inside a transaction

10. Escalating a Lock inside a transaction

11. Locks inside session, not transaction?

12. Triggers, Stored Procedures and user permission to call an Extended Procedure


 
Powered by phpBB® Forum Software