Help with Locking and Cascade Delete 1

[itflash]

Hi all

Really need some help with lock and cascade deletes.

My app needs pesimistic locking.
So i lock records using (updlock, rowlock).


Scenario

The design is, Table 1 has many records in Table 2.

User 1 locks a record for edit in Table 2 (with a trans).

User 2 deletes an unrelated record in Table 1 which will delete related records in Table 2 (which are not locked).

I cannot get this to work. Even though user 2 is deleting records that are not locked, it always times out.
I have tried triggers and RI in a diagram, but it wont work.

Any help is greatly appreciated.

Thanks
ITflash

 

[jgoodman00]

Nope, on mine it executes perfectly with no conflicting locks.

If you look at the locks in lock manager whilst this is going on, are any deadlocks shown?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[itflash]

OK - there must have been some old locks in there I/someone left behind, restarted the SQL Server and it did the trick.


When I run your example:

The preview pane is split into two results, one returns 7 rows and the other returns no rows.

 

[itflash]

I must be doing something wrong.


Looking back at my Application, when I amend a TASK (pessimistic lock), the following locks are generated.

Object	Lock Type	Mode	Status	Owner	Index	Resource
MyDB	DB	S	GRANT	Sess		                
MyDB.dbo.Task	PAG	IU	GRANT	Xact	PK_Task	1:174           
MyDB.dbo.Task	TAB	IX	GRANT	Xact		                
MyDB.dbo.Task	KEY	U	GRANT	Xact	PK_Task	(1c00c4c874c4)  
MyDB.dbo.Task	PAG	IU	GRANT	Xact	tTask	1:108           
MyDB.dbo.Task	RID	U	GRANT	Xact	tTask	1:108:4         
MyDB.dbo.Task	RID	U	GRANT	Xact	tTask	1:108:5

If at the same time I try the delete,

Object	Lock Type	Mode	Status	Owner	Index	Resource
MyDB	DB	S	GRANT	Sess		                
MyDB.dbo.Contract	KEY	X	GRANT	Xact	PK_Contract	(230033203c6c)  
MyDB.dbo.Contract	PAG	IX	GRANT	Xact	PK_Contract	1:137           
MyDB.dbo.Contract	TAB	IX	GRANT	Xact		                
MyDB.dbo.Contract	PAG	IX	GRANT	Xact	tContract	1:90            
MyDB.dbo.Contract	RID	X	GRANT	Xact	tContract	1:90:2          
MyDB.dbo.Contract	RID	X	GRANT	Xact	tContract	1:90:1          
MyDB.dbo.Task	TAB	IX	GRANT	Xact		                
MyDB.dbo.Time	TAB	IX	GRANT	Xact

Also:
In the Locks/Process ID in EM

The top one says (Blocking) and the bottom one says (Blocked by 53)

 

[jgoodman00]

Have you enclosed the delete statement inside a transaction?

Can you try this?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[itflash]

Figured it out

Couldn't have done it without your help/advice and I have learnt a lot just from this thread. You help is appreciated and just want to thank you again.

The problem was..... I did not have an index on the foreign keys that linked the 3 tables. As soon as I indexed the fields, it worked.

Thanks again
ITflash