Blocking Issue?

Question

Blocking Issue?

mike 57299

SSCrazy

Points: 2529
More actions
May 15, 2010 at 10:17 pm

#232602

Hi All.
I have table:
* Web_processing_queue
-- Web_processing_queue_id (int) identity(1,1)
-- task varchar(200)
-- processed_fl bit
I have the following trigger on Web_processing_queue
ALTER trigger [dbo].[tr_web_processing_queue] on
[dbo].[web_processing_queue] after INSERT AS
begin
DECLARE @id id
SELECT @id = IDENT_CURRENT('web_processing_queue')
exec usp_web_processing_queue @id
end
I have the following stored proc:
ALTER PROCEDURE [dbo].[usp_web_processing_queue]( @id id=NULL)
AS
begin
declare @cID varchar(200)
declare @cPgm varchar(1000)
set @cID = CAST( @id AS varchar(20))
set @cPgm = 'c:\om5\web_processing_queue.exe '+ @cid
EXEC master..xp_CMDShell @cPgm
end
In the external program, I get a link to the data via SQL native driver, and I want to change a processed_fl (bit) from 0 to 1 for the record that caused the trigger call.
I have the trigger set to "AFTER" but it still hangs. Any ideas?
Thanks,
Mike

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Yes... step 1... fix the following code... "ID" isn't a valid datatype unless they've done something really different in 2k8 or you have a user type squirreled away somewhere that you haven't identified.

DECLARE @id id

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

mike 57299 SSCrazy Points: 2529 More actions · Answer 2

Jeff

Oops. I do have a system defined data type of ID. It is an INT.

Mike

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Ok... The next step is to find out where the hanging occurs...

What happens if you run the .exe command from a DOS window?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

mike 57299 SSCrazy Points: 2529 More actions · Answer 4

When I run it from DOS, it works fine. No hang. Debug messages get written. It hangs on the 'update web_processing_queue...' statement.

Mike

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

A trigger executed within the transaction that fired it. The transaction is not committed, and hence the locks are not released, until the trigger completes. Hence, if another connection (like your dos app) tries to access that row, it will be blocked.

I'm not sure what it is that you're trying to do here. What does that app do?

It's also worth nothing that it's usually a bad idea to call external apps from a trigger, because they extend the length of the transaction and hence the length of time that locks are held. Plus, if the trigger fails, it rolls back the change that fired it, because it's still in a transaction.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mike 57299 SSCrazy Points: 2529 More actions · Answer 6

Gail,

What I am trying to do is the following:

* Website customer creates order in Shopping cart header / detail tables

* When customer says place order, website puts record in queue with a request type of 'NEW ORDER'.

* Trigger on queue causes small exe to fire that will populate an order with all the extra stuff needed into the normal order_hdr and order_dtl files.

* Since it is an external small exe, it can fire as many times a request is created.

Does this make sense?

Mike

:ermm: P.S. Don't hate me... but I am using Visual Foxpro. I Don't know .Net stuff yet.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

mike 57299 (5/16/2010)
When I run it from DOS, it works fine. No hang. Debug messages get written. It hangs on the 'update web_processing_queue...' statement.
Mike

Hmmm... does the app write to the screen and does it ask any questions that require human interaction? Also, have you remembered to close and drop any connections that may have been opened by the app?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

mike 57299 SSCrazy Points: 2529 More actions · Answer 8

Jeff,

The connections close when I close the exe. I have tested this issue by issuing an INSERT command in the command window. It hangs until I use the task manager to kill the exe process. Then it finishes.

Mike

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

mike 57299 (5/16/2010)
I have tested this issue by issuing an INSERT command in the command window. It hangs until I use the task manager to kill the exe process. Then it finishes.

Yes, it will.

If the exe is trying to access the same row that was changed to fire the trigger, it will be blocked and have to wait, because that row is still locked by the transaction that the trigger (and its triggering operation) is running in. The trigger can't finish (commit the transaction and release the lock) until the exe completes, but the exe cannot complete until the trigger finishes because it needs to access to a row that the trigger has locked.

You're pretty much managed to cause the equivalent of a deadlock (though it isn't one exactly)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

mike 57299 (5/16/2010)
* Trigger on queue causes small exe to fire that will populate an order with all the extra stuff needed into the normal order_hdr and order_dtl files.
* Since it is an external small exe, it can fire as many times a request is created.

Can this be done inside SQL, with a stored procedure or similar? The problem is that you're spawning a new connection and trying to access a row that's locked until the trigger completes. If you can do whatever population is necessary in a stored proc, it'll run within the context of the transaction that the trigger is in and hence won't be blocked.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

GilaMonster (5/16/2010)
mike 57299 (5/16/2010)
I have tested this issue by issuing an INSERT command in the command window. It hangs until I use the task manager to kill the exe process. Then it finishes.
Yes, it will.
If the exe is trying to access the same row that was changed to fire the trigger, it will be blocked and have to wait, because that row is still locked by the transaction that the trigger (and its triggering operation) is running in. The trigger can't finish (commit the transaction and release the lock) until the exe completes, but the exe cannot complete until the trigger finishes because it needs to access to a row that the trigger has locked.
You're pretty much managed to cause the equivalent of a deadlock (though it isn't one exactly)

BWAA-HAA!!! The only way the .exe would work, then, is if it did a dirty read. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

Jeff Moden (5/16/2010)
GilaMonster (5/16/2010)
mike 57299 (5/16/2010)
I have tested this issue by issuing an INSERT command in the command window. It hangs until I use the task manager to kill the exe process. Then it finishes.
Yes, it will.
If the exe is trying to access the same row that was changed to fire the trigger, it will be blocked and have to wait, because that row is still locked by the transaction that the trigger (and its triggering operation) is running in. The trigger can't finish (commit the transaction and release the lock) until the exe completes, but the exe cannot complete until the trigger finishes because it needs to access to a row that the trigger has locked.
You're pretty much managed to cause the equivalent of a deadlock (though it isn't one exactly)
BWAA-HAA!!! The only way the .exe would work, then, is if it did a dirty read. 🙂

Which it can't, since it's updating the row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

GilaMonster (5/16/2010)
Jeff Moden (5/16/2010)
GilaMonster (5/16/2010)
mike 57299 (5/16/2010)
I have tested this issue by issuing an INSERT command in the command window. It hangs until I use the task manager to kill the exe process. Then it finishes.
Yes, it will.
If the exe is trying to access the same row that was changed to fire the trigger, it will be blocked and have to wait, because that row is still locked by the transaction that the trigger (and its triggering operation) is running in. The trigger can't finish (commit the transaction and release the lock) until the exe completes, but the exe cannot complete until the trigger finishes because it needs to access to a row that the trigger has locked.
You're pretty much managed to cause the equivalent of a deadlock (though it isn't one exactly)
BWAA-HAA!!! The only way the .exe would work, then, is if it did a dirty read. 🙂
Which it can't, since it's updating the row.

Ummm... you sure? WITH(NOLOCK) can be used in T-SQL to read updated rows that haven't been commited yet... why would an app be any different especially if it were using SQL with a locking hint to read the row? And. from the description the OP gave us, I don't believe the .exe is modifying the same row which, of course, would be impossible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

Jeff Moden (5/16/2010)
Ummm... you sure? WITH(NOLOCK) can be used in T-SQL to read updated rows that haven't been commited yet...

Yes, it can be used to read them, but if you look at the initial post, the OP wants the app to UPDATE the row, not just to read it.

If it was just reading, I'd suggest read uncommitted or one of the snapshots, but neither will help here. Hence the reason I'm asking if whatever the app does can be moved inside SQL so that it can run in context of the transaction, not in a separate connection.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass