question: row locking w/ triggers

[agar]

Question: how do transactions and locking work w/ triggers? Let's say I have a table with an update trigger defined on it. I execute a query that updates row N in that table. Which, if any, of the following occur, and if none than what does occur?

Scenario A
1. begin transaction
2. the job obtains an exclusive lock on row N and the update is made
3. the trigger executes
4. the lock on row N is released (end transaction)


Scenario B
1. the trigger executes (begin + end transaction)
2. begin new transaction
3. the job obtains an exclusive lock on row N and the update is made
4. the lock on row N is released (end transaction)


Scenario C
1. begin transaction
2. the job obtains an exclusive lock on row N and the update is made
3. the lock on row N is released (end transaction)
4. the trigger executes (begin and end new transaction)


On the one hand, it might seem that scenario B or C is the correct one, because the documentation for triggers states that if a "rollback transaction" statement occurs within a trigger, it will roll back the transaction that contains the insert/update/delete statement that caused the trigger to occur. However, I just tried putting an infinite while loop in a trigger and using a different connection to query the table that the trigger is attached to, and the second connection blocked, probably waiting for the while loop to finish.

Any help on this is appreciated -- thanks in advance.

 

[rajeevnandanmishra]

Hi there,
Begin Transaction command, by itself, doesn't create an exclusive lock. The commands following begin transaction may aquire exclusive lock.

From your tips, i can suggest the first scenario will happen:

If you can give your procedure+trigger that which statements are you using and where, may be it will help us to understand your problem in better way.

 

[agar]

here are the "create table" statements

create table widget (widgetid int, widgetcolor int, ... blahblah ...)
create table widgetbin (widgetid int PRIMARY KEY, ... blahblah ...)

here's the problem code:

declare @i int, @n int, @widgetid int

... set variables ...


set @i = 0
while (@i < @N)
 begin
	select top 1 @widgetid = widget.widgetid
	 from widget
	 where ...
	  and widget.widgetid not in (
		select widgetid
		 from widgetbin
	   )
	 order by widget.widgetcolor desc

	if (@widgetid is null)
	 break

	insert into widgetbin (widgetid, ...some other stuff...)
	 values (@widgetid, ...some other stuff...)

	set @i = @i + 1
 end

So what's the problem? The problem is that somehow I'm getting a primary key constraint failure on the second insert into widgetbin, despite the "not in" statement. The reason I thought it was a trigger problem is that "widgetid" is a unique number that's assigned to each record in the "widget" table in a trigger, but it's not an actual primary key in that table. I wasn't even thinking straight anyway, this couldn't have caused the problem -- but I have no idea what could. The record is inserted into widgetbin, then we select the next record from the widget table that's not already in the widgetbin table. But somehow the "not in" clause is not working, and the second record is the same as the first.

What I want to know is this -- is there an easier way to do this? I want to select the top N records of a table, but I want "N" to be dynamic, not hardcoded -- and I can't use "set rowcount" because I need to use order by. Is there any way I can do this without a while loop?

Thanks for the help

 

[agar]

ARRGH! I just figured out the problem. Of course, when you do this:

set @x = 4
select @x = x
 from my_abilities
 where name = 'programming SQL'

if there is no such record in the my_abilities table, then @x will remain at 4 and will not get set to NULL. I deserve a few lashes for that.

 

[rajeevnandanmishra]

Hi there,
From your code and problem it looks that widgetid in widget table is not unique.(Just for confirmation be sure of this.)

What you can try is something like this :

INSERT WIDGETBIN
SELECT widget.widgetid, ...other stuff... FROM
widget WHERE widget.widgetid not in
(select widgetid from widgetbin )


Please let me know how it works.

I am yet not sure if widget table have unique widgetid.(Sorry but please confirm that first).

 

[agar]

Hi,

Thanks for your help. When the problem occurred, I did a few selects on the database to confirm that, in fact, widgetid was unique in the widget table, and it was.

The problem (as I just figured out) is that the first time through the loop, the variable @widgetid gets set to some number (let's say 34). Then, the next time through the loop, there are no more records in the widget table that fulfil the where clause. I thought that that would set @widgetid to NULL and then the clause "if (@widgetid is null) break" would be executed. But I forgot that in variable-assignment select statements like the one in the code above, if no records fulfil the where clause, then the variable that's being assigned to remains at whatever value it was already at -- so the second time through the loop @widgetid still = 34, and you get the PK error when you try to insert it again. For some reason I was thinking it would get set to null and then we'd break out of the loop. Dumb me.