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]

Are you creating application locks or letting SQL Server manage them?

What locks are given to User1 given when they make the edit?
What locks are given to User2 when they attempt the delete?


James Goodman MCSE, MCDBA

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

 

[itflash]

Thanks for the reply

user 1 gets a lock by my application doing a sql pass through statement "with (uplock,rowlock)" and a transaction.

user 2 only has a transaction with the sql pass through command. No uplock or rowlock.

ITflash

 

[jgoodman00]

What is the granularity of each of the locks?
e.g. table, row etc

You can get this information from EM...

James Goodman MCSE, MCDBA

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

 

[itflash]

Thanks for the reply


Before User Locks record for Edit

spid dbid ObjID IndID Type Resource Mode Status

51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
54 10 0 0 DB S GRANT
56 1 85575343 0 TAB IS GRANT



After User Locks record for Edit


spid dbid ObjID IndID Type Resource Mode Status
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
54 10 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
57 10 85575343 0 TAB IX GRANT
57 10 85575343 255 RID 1:128:2 U GRANT
57 10 85575343 1 KEY (2000dd8f897e) U GRANT
57 10 85575343 255 PAG 1:128 IU GRANT
57 10 85575343 1 PAG 1:142 IU GRANT
57 10 85575343 255 RID 1:128:0 U GRANT
57 10 0 0 DB S GRANT



Thanks
ITflash

 

[itflash]

Any ideas anyone?

 

[jgoodman00]

The problem would appear to be with SPID 57 which holds an Exclusive Intent Table lock. This is going to prevent any other SPIDS getting a lock on the table...

What is the TSQL being executed by each user?

James Goodman MCSE, MCDBA

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

 

[itflash]

Thanks for the reply


From within my code (FoxPro).

(1)
User selects a record to edit.

(2)
I start a transaction with a ConnectTimeOut of 5 and a QueryTimeOut of 5.

(3)
Get back the data the user wants to edit and some other display data (hence the joins).

SELECT task.taskid, contract.companyid, task.contractid,
task.taskdesc, task.tstatusid, task.tcatid,
contractstatus.live as contractlive,
task.tcatid, task.solution, task.notes,
CompanyUser.cuserid, CompanyUser.cusername, CompanySite.csiteid, CompanySite.csitedesc,
task.scatid
FROM task WITH (updlock, rowlock)
LEFT JOIN contract ON contract.contractid=task.contractid
LEFT JOIN contractstatus ON contractstatus.cstatusid=contract.cstatusid
LEFT JOIN CompanyUser ON CompanyUser.cuserid=task.cuserid
LEFT JOIN CompanySite ON CompanySite.csiteid=CompanyUser.csiteid
LEFT JOIN company on company.companyid=contract.companyid
WHERE task.taskid=3

(4) User edits the data, clicks save and using the same connection handle - issue an Update Statement to update only the TASK table.



Thanks
ITflash

 

[jgoodman00]

Ahhh, I think I understand now.

Your problem will be in relation to point 2. Basically you are creating a long-running transaction by setting a lock before the user edits the data.

Is it possible to modify this workflow so that the transaction only begins when the user actually attempts to commit the record? At the moment you are generating locks, & starting a transaction (& generating log activity) even though the user could be sometime modifying the record.

So, I would suggest reworking to something like:
1. User selects record to edit.
2. Fetch the data.
3. User edits data.
4. User commits data.
5. A transaction is started & the data is committed.
6. Transaction ends.


This way your transaction will only be for the duration of the of the update, rather than the duration of the edit session...


James Goodman MCSE, MCDBA

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

 

[itflash]

Thanks for the reply.

The application needs to be pesimisstic, that is why I put the transaction at the start. I need to lock the TASK record for the duration of the edit.

Is there any way I can lock the record in the TASK table without a transaction? I tried the SELECT statement without the transaction and it does not lock the record.

Just out of interest, whilst one user is editing, other users can still add, update, delete records in the TASK table. It is only the Cascade Delete that does not work from a table above.

Thanks
ITflash

 

[itflash]

anyone any ideas?

Thanks
ITflash

 

[jgoodman00]

Sorry for the delay. Am in the process of moving house...

When user 2 attempts the delete & user 1 holds no locks, what locks are created?

James Goodman MCSE, MCDBA

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

 

[itflash]

Thanks for the reply

Don't no know how I can get these locks to display,
I normally run sp_lock in query analyser and the profiler trace does not seem to have the same columns.

Thanks
ITflash

 

[itflash]

any ideas?

Thanks
ITflash

 

[jgoodman00]

Whoops, I must have replied in the wrong thread...

In profiler:
For events select Lock Acquired, Lock Released, & Lock Deadlock.

For data-columns select Mode & Binary data.

This should give you the spid, lock type & the lock mode...

James Goodman MCSE, MCDBA

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

 

[itflash]

Thanks for the reply. You probably confused somebody somewhere with a reply in another thread

The trace is very big - hope this is useful to you.

From what I can make out, the following is the most useful (between the set of the transaction type and the completed delete statement).

Looking down the list I can see at least one table lock.


Thanks
ITflash


SQL:BatchCompleted set implicit_transactions on
Lock:Acquired 3 0X07A0400010BF1D7301CAB
Lock:Released 0X07A0400010BF1D7301CAB
Lock:Acquired 3 0X07A0100020871D1999582
Lock:Acquired 3 0X07A010001029079ACF
Lock:Acquired 1 0X05A0293FAC4B00000000
Lock:Released 0X07A010001029079ACF
Lock:Released 0X07A0100020871D1999582
Lock:Acquired 3 0X07A0100020871D1999582
Lock:Acquired 3 0X07A010001029079ACF
Lock:Released 0X07A010001029079ACF
Lock:Released 0X07A0100020871D1999582
Lock:Acquired 3 0X07A03000203824F4662F0
Lock:Acquired 3 0X07A03000102A0F8538EDB
Lock:Released 0X07A03000102A0F8538EDB
Lock:Released 0X07A03000203824F4662F0
Lock:Acquired 3 0X07A010001029079ACF
Lock:Released 0X07A010001029079ACF
Lock:Acquired 3 0X07A010001029079ACF
Lock:Released 0X07A010001029079ACF
Lock:Acquired 3 0X07A03000102A0F8538EDB
Lock:Released 0X07A03000102A0F8538EDB
Lock:Acquired 3 0X07A03000102B016FC3BC9
Lock:Released 0X07A03000102B016FC3BC9
Lock:Acquired 3 0X07A03000102C0739B8771
Lock:Released 0X07A03000102C0739B8771
Lock:Acquired 3 0X07A03000102D0CAA350EC
Lock:Released 0X07A03000102D0CAA350EC
Lock:Acquired 3 0X07A03000102E0AFC4EC54
Lock:Released 0X07A03000102E0AFC4EC54
Lock:Acquired 3 0X07A03000102F0416B5946
Lock:Released 0X07A03000102F0416B5946
Lock:Acquired 3 0X07A030001030024CE5FE
Lock:Released 0X07A030001030024CE5FE
Lock:Acquired 3 0X07A0300010310721C86A6
Lock:Released 0X07A0300010310721C86A6
Lock:Acquired 3 0X07A0300010320177B3A1E
Lock:Released 0X07A0300010320177B3A1E
Lock:Acquired 3 0X07A0300010330F9D48FC
Lock:Released 0X07A0300010330F9D48FC
Lock:Acquired 3 0X07A03000103409CB333B4
Lock:Released 0X07A03000103409CB333B4
Lock:Acquired 3 0X07A0300010350258BE429
Lock:Released 0X07A0300010350258BE429
Lock:Acquired 3 0X07A030001036040EC5891
Lock:Released 0X07A030001036040EC5891
Lock:Acquired 3 0X07A0300010370AE43ED83
Lock:Released 0X07A0300010370AE43ED83
Lock:Acquired 3 0X07A02000102A028D0862F
Lock:Acquired 3 0X07A02000102A028D0862F
Lock:Acquired 6 0X06A09800010000000
Lock:Acquired 3 0X09A09800010100000
Lock:Released 0X06A09800010000000
Lock:Released 0X09A09800010100000
Lock:Released 0X07A02000102A028D0862F
Lock:Released 0X07A02000102A028D0862F
Lock:Acquired 3 0X07A02000102B0EB83AB4
Lock:Released 0X07A02000102B0EB83AB4
Lock:Acquired 3 0X07A02000102C0AAB2B01D
Lock:Released 0X07A02000102C0AAB2B01D
Lock:Acquired 3 0X07A02000102D06D24F152
Lock:Released 0X07A02000102D06D24F152
Lock:Acquired 3 0X07A02000102E02C15EA4B
Lock:Released 0X07A02000102E02C15EA4B
Lock:Acquired 3 0X07A02000102F0EF46C760
Lock:Released 0X07A02000102F0EF46C760
Lock:Acquired 3 0X07A0200010300AE77DC79
Lock:Released 0X07A0200010300AE77DC79
Lock:Acquired 3 0X07A0200010310616B44FE
Lock:Released 0X07A0200010310616B44FE
Lock:Acquired 3 0X07A0200010320205A5FE7
Lock:Released 0X07A0200010320205A5FE7
Lock:Acquired 3 0X07A0200010330E3972CC
Lock:Released 0X07A0200010330E3972CC
Lock:Acquired 3 0X07A0200010340A23869D5
Lock:Released 0X07A0200010340A23869D5
Lock:Acquired 3 0X07A020001035065AE289A
Lock:Released 0X07A020001035065AE289A
Lock:Acquired 3 0X07A02000102811B1CB9A5
Lock:Released 0X07A02000102811B1CB9A5
Lock:Acquired 3 0X07A02000102A028D0862F
Lock:Acquired 3 0X07A02000102A028D0862F
Lock:Acquired 6 0X06A09800010000000
Lock:Acquired 3 0X09A09800010100000
Lock:Released 0X06A09800010000000
Lock:Released 0X09A09800010100000
Lock:Released 0X07A02000102A028D0862F
Lock:Released 0X07A02000102A028D0862F
Lock:Acquired 3 0X07A02000102B0EB83AB4
Lock:Released 0X07A02000102B0EB83AB4
Lock:Acquired 3 0X07A02000102C0AAB2B01D
Lock:Released 0X07A02000102C0AAB2B01D
Lock:Acquired 3 0X07A02000102D06D24F152
Lock:Released 0X07A02000102D06D24F152
Lock:Acquired 3 0X07A02000102E02C15EA4B
Lock:Released 0X07A02000102E02C15EA4B
Lock:Acquired 3 0X07A02000102F0EF46C760
Lock:Released 0X07A02000102F0EF46C760
Lock:Acquired 3 0X07A0200010300AE77DC79
Lock:Released 0X07A0200010300AE77DC79
Lock:Acquired 3 0X07A0200010310616B44FE
Lock:Released 0X07A0200010310616B44FE
Lock:Acquired 3 0X07A0200010320205A5FE7
Lock:Released 0X07A0200010320205A5FE7
Lock:Acquired 3 0X07A0200010330E3972CC
Lock:Released 0X07A0200010330E3972CC
Lock:Acquired 3 0X07A0200010340A23869D5
Lock:Released 0X07A0200010340A23869D5
Lock:Acquired 3 0X07A020001035065AE289A
Lock:Released 0X07A020001035065AE289A
Lock:Acquired 3 0X07A02000102811B1CB9A5
Lock:Released 0X07A02000102811B1CB9A5
Lock:Acquired 3 0X07A0E000307206D772D62
Lock:Acquired 3 0X07A0E000104901363A4DF
Lock:Acquired 3 0X07A01000104901363A4DF
Lock:Acquired 3 0X07A0100010AF0D59EFBB5
Lock:Released 0X07A0100010AF0D59EFBB5
Lock:Acquired 3 0X07A0100010AF0D59EFBB5
Lock:Acquired 1 0X05A0AFC619500000000
Lock:Released 0X07A0100010AF0D59EFBB5
Lock:Acquired 3 0X07A0100010AF0D59EFBB5
Lock:Released 0X07A0100010AF0D59EFBB5
Lock:Acquired 3 0X07A0300010B609D9B9337
Lock:Released 0X07A0300010B609D9B9337
Lock:Acquired 3 0X07A01000104901363A4DF
Lock:Released 0X07A01000104901363A4DF
Lock:Acquired 3 0X07A0300010B609D9B9337
Lock:Released 0X07A0300010B609D9B9337
Lock:Acquired 3 0X07A0100010AF0D59EFBB5
Lock:Released 0X07A0100010AF0D59EFBB5
Lock:Released 0X07A0E000104901363A4DF
Lock:Released 0X07A01000104901363A4DF
Lock:Released 0X07A0E000307206D772D62
Lock:Acquired 3 0X07A0300010B0041C4F812
Lock:Released 0X07A0300010B0041C4F812
Lock:Acquired 3 0X07A0300010B10AF6B4D0
Lock:Released 0X07A0300010B10AF6B4D0
Lock:Acquired 3 0X07A0300010B20CACF1B8
Lock:Released 0X07A0300010B20CACF1B8
Lock:Acquired 3 0X07A0300010B3073342625
Lock:Released 0X07A0300010B3073342625
Lock:Acquired 3 0X07A0300010B4016539A9D
Lock:Released 0X07A0300010B4016539A9D
Lock:Acquired 3 0X07A0300010B50F8FC2F8F
Lock:Released 0X07A0300010B50F8FC2F8F
Lock:Acquired 3 0X07A0300010B609D9B9337
Lock:Released 0X07A0300010B609D9B9337
Lock:Acquired 3 0X07A0300010B70CB8BF06F
Lock:Released 0X07A0300010B70CB8BF06F
Lock:Acquired 3 0X07A0300010B80AEEC4CD7
Lock:Released 0X07A0300010B80AEEC4CD7
Lock:Acquired 3 0X07A0300010BB09C1C92E0
Lock:Released 0X07A0300010BB09C1C92E0
Lock:Acquired 3 0X07A0200010B00A3BE3F2
Lock:Released 0X07A0200010B00A3BE3F2
Lock:Acquired 3 0X07A0200010B106058CED9
Lock:Released 0X07A0200010B106058CED9
Lock:Acquired 3 0X07A0200010B202169D5C0
Lock:Released 0X07A0200010B202169D5C0
Lock:Acquired 3 0X07A0200010B30E6FF948F
Lock:Released 0X07A0200010B30E6FF948F
Lock:Acquired 3 0X07A0200010B40A7CE8F96
Lock:Released 0X07A0200010B40A7CE8F96
Lock:Acquired 3 0X07A0200010B50649DA2BD
Lock:Released 0X07A0200010B50649DA2BD
Lock:Acquired 3 0X07A0200010B6025ACB9A4
Lock:Released 0X07A0200010B6025ACB9A4
Lock:Acquired 3 0X07A0200010B70EAB02123
Lock:Released 0X07A0200010B70EAB02123
Lock:Acquired 3 0X07A0200010AE190C7DC78
Lock:Released 0X07A0200010AE190C7DC78
Lock:Acquired 3 0X07A0E000304F11C564A7
Lock:Acquired 3 0X07A0E00010A00DB869BD3
Lock:Acquired 3 0X07A0100010A00DB869BD3
Lock:Acquired 3 0X07A0100010D4078C29EC0
Lock:Released 0X07A0100010D4078C29EC0
Lock:Acquired 3 0X07A0100010D4078C29EC0
Lock:Acquired 1 0X05A0D4AB884E00000000
Lock:Released 0X07A0100010D4078C29EC0
Lock:Acquired 3 0X07A0100010D4078C29EC0
Lock:Released 0X07A0100010D4078C29EC0
Lock:Acquired 3 0X07A0300010D60F267FE2
Lock:Released 0X07A0300010D60F267FE2
Lock:Acquired 3 0X07A0100010A00DB869BD3
Lock:Released 0X07A0100010A00DB869BD3
Lock:Acquired 3 0X07A0300010D60F267FE2
Lock:Released 0X07A0300010D60F267FE2
Lock:Acquired 3 0X07A0100010D4078C29EC0
Lock:Released 0X07A0100010D4078C29EC0
Lock:Released 0X07A0E00010A00DB869BD3
Lock:Released 0X07A0100010A00DB869BD3
Lock:Released 0X07A0E000304F11C564A7
Lock:Acquired 3 0X07A0300010D501CC8BAF0
Lock:Released 0X07A0300010D501CC8BAF0
Lock:Acquired 3 0X07A0300010D60F267FE2
Lock:Released 0X07A0300010D60F267FE2
Lock:Acquired 3 0X07A0300010D70970B35A
Lock:Released 0X07A0300010D70970B35A
Lock:Acquired 3 0X07A0300010D802E3864C7
Lock:Released 0X07A0300010D802E3864C7
Lock:Acquired 3 0X07A0300010D904B5FD87F
Lock:Released 0X07A0300010D904B5FD87F
Lock:Acquired 3 0X07A0300010DA0A5F06D6D
Lock:Released 0X07A0300010DA0A5F06D6D
Lock:Acquired 3 0X07A0300010DB0C097D1D5
Lock:Released 0X07A0300010DB0C097D1D5
Lock:Acquired 3 0X07A0300010DC09687B28D
Lock:Released 0X07A0300010DC09687B28D
Lock:Acquired 3 0X07A0300010DD0F3E0E35
Lock:Released 0X07A0300010DD0F3E0E35
Lock:Acquired 3 0X07A0200010D50BDA1978B
Lock:Released 0X07A0200010D50BDA1978B
Lock:Acquired 3 0X07A0200010D607EF2BAA0
Lock:Released 0X07A0200010D607EF2BAA0
Lock:Acquired 3 0X07A0200010D703FC3A1B9
Lock:Released 0X07A0200010D703FC3A1B9
Lock:Acquired 3 0X07A0200010D80F855E0F6
Lock:Released 0X07A0200010D80F855E0F6
Lock:Acquired 3 0X07A0200010D90B964FBEF
Lock:Released 0X07A0200010D90B964FBEF
Lock:Acquired 3 0X07A0200010DA07A37D6C4
Lock:Released 0X07A0200010DA07A37D6C4
Lock:Acquired 3 0X07A0200010DB03B6CDDD
Lock:Released 0X07A0200010DB03B6CDDD
Lock:Acquired 3 0X07A0200010DC0F41A555A
Lock:Released 0X07A0200010DC0F41A555A
Lock:Acquired 3 0X07A0200010D318E6DA81
Lock:Released 0X07A0200010D318E6DA81
Lock:Acquired 8 0X05A0AFC619500000000
Lock:Acquired 8 0X05A0293FAC4B00000000
Lock:Acquired 8 0X05A0D4AB884E00000000
Lock:Acquired 8 0X06A08900010000000
Lock:Acquired 5 0X07A0293FAC4B101F02A67C1D6
Lock:Acquired 5 0X0820F800010000000
Lock:Acquired 4 0X08204800010000000
Lock:Acquired 5 0X06204E00010000000
Lock:Acquired 5 0X08204800010000000
Lock:Acquired 5 0X06204800010000000
Lock:Acquired 5 0X06204900010000000
Lock:Acquired 5 0X06204A00010000000
Lock:Acquired 5 0X06204B00010000000
Lock:Acquired 5 0X06204C00010000000
Lock:Acquired 5 0X06204D00010000000
Lock:Released 0X06204800010000000
Lock:Released 0X06204900010000000
Lock:Released 0X06204A00010000000
Lock:Released 0X06204B00010000000
Lock:Released 0X06204C00010000000
Lock:Released 0X06204D00010000000
Lock:Released 0X08204800010000000
Lock:Released 0X08204800010000000
Lock:Released 0X06204E00010000000
Lock:Released 0X0820F800010000000
Lock:Acquired 6 0X06A05A00010000000
Lock:Acquired 3 0X09A05A00010100000
Lock:Acquired 8 0X06A05A00010000000
Lock:Acquired 5 0X09A05A00010100000
Lock:Acquired 8 0X06A05A00010000000
Lock:Acquired 5 0X09A05A00010100000
Lock:Released 0X06A05A00010000000
Lock:Released 0X09A05A00010100000
Lock:Acquired 6 0X06A05A00010000000
Lock:Acquired 3 0X09A05A00010200000
Lock:Acquired 8 0X06A05A00010000000
Lock:Acquired 5 0X09A05A00010200000
Lock:Acquired 8 0X06A05A00010000000
Lock:Acquired 5 0X09A05A00010200000
Lock:Released 0X06A05A00010000000
Lock:Released 0X09A05A00010200000
Lock:Acquired 8 0X010000F03FF83D541AB03E541A
Lock:Acquired 7 0X06A0AE00010000000
Lock:Acquired 4 0X07A0AFC6195101C0C4C874C4
Lock:Released 0X07A0AFC6195101C0C4C874C4
Lock:Acquired 4 0X07A0AFC6195103A026382D41
Lock:Released 0X07A0AFC6195103A026382D41
Lock:Acquired 4 0X07A0AFC6195103B0435F91F9
Lock:Released 0X07A0AFC6195103B0435F91F9
Lock:Acquired 4 0X07A0AFC6195103C0FA674664
Lock:Released 0X06A0AE00010000000
Lock:Released 0X07A0AFC6195103C0FA674664
Lock:Acquired 7 0X06A0AE00010000000
Lock:Acquired 4 0X07A0AFC6195103A026382D41
Lock:Acquired 5 0X0820F000010000000
Lock:Acquired 4 0X08204800010000000
Lock:Acquired 5 0X06204F00010000000
Lock:Acquired 5 0X08204800010000000
Lock:Acquired 5 0X06204800010000000
Lock:Acquired 5 0X06204900010000000
Lock:Acquired 5 0X06204A00010000000
Lock:Acquired 5 0X06204B00010000000
Lock:Acquired 5 0X06204C00010000000
Lock:Acquired 5 0X06204D00010000000
Lock:Acquired 5 0X06204E00010000000
Lock:Released 0X06204800010000000
Lock:Released 0X06204900010000000
Lock:Released 0X06204A00010000000
Lock:Released 0X06204B00010000000
Lock:Released 0X06204C00010000000
Lock:Released 0X06204D00010000000
Lock:Released 0X06204E00010000000
Lock:Released 0X08204800010000000
Lock:Released 0X08204800010000000
Lock:Released 0X06204F00010000000
Lock:Released 0X0820F000010000000
Lock:Acquired 5 0X07A0AFC6195103A026382D41
Lock:Released 0X07A0AFC6195103A026382D41
Lock:Acquired 8 0X06A0AE00010000000
Lock:Acquired 6 0X06A06C00010000000
Lock:Acquired 3 0X09A06C00010000000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010000000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010000000
Lock:Released 0X06A06C00010000000
Lock:Released 0X09A06C00010000000
Lock:Acquired 6 0X06A06C00010000000
Lock:Acquired 3 0X09A06C00010100000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010100000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010100000
Lock:Released 0X06A06C00010000000
Lock:Released 0X09A06C00010100000
Lock:Released 0X06A0AE00010000000
Lock:Acquired 7 0X06A0AE00010000000
Lock:Released 0X07A0AFC6195103A026382D41
Lock:Acquired 4 0X07A0AFC6195103B0435F91F9
Lock:Acquired 5 0X07A0AFC6195103B0435F91F9
Lock:Released 0X07A0AFC6195103B0435F91F9
Lock:Acquired 8 0X06A0AE00010000000
Lock:Acquired 6 0X06A06C00010000000
Lock:Acquired 3 0X09A06C00010200000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010200000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010200000
Lock:Released 0X06A06C00010000000
Lock:Released 0X09A06C00010200000
Lock:Acquired 6 0X06A06C00010000000
Lock:Acquired 3 0X09A06C00010300000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010300000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010300000
Lock:Released 0X06A06C00010000000
Lock:Released 0X09A06C00010300000
Lock:Released 0X06A0AE00010000000
Lock:Acquired 7 0X06A0AE00010000000
Lock:Released 0X07A0AFC6195103B0435F91F9
Lock:Acquired 4 0X07A0AFC6195103C0FA674664
Lock:Acquired 5 0X07A0AFC6195103C0FA674664
Lock:Released 0X07A0AFC6195103C0FA674664
Lock:Acquired 8 0X06A0AE00010000000
Lock:Acquired 6 0X06A06C00010000000
Lock:Acquired 3 0X09A06C00010600000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010600000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010600000
Lock:Released 0X06A06C00010000000
Lock:Released 0X09A06C00010600000
Lock:Acquired 6 0X06A06C00010000000
Lock:Acquired 3 0X09A06C00010700000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010700000
Lock:Acquired 8 0X06A06C00010000000
Lock:Acquired 5 0X09A06C00010700000
Lock:Released 0X06A06C00010000000
Lock:Released 0X09A06C00010700000
Lock:Released 0X07A0AFC6195103C0FA674664
Lock:Released 0X06A0AE00010000000
Lock:Acquired 8 0X010000F03F0000B03E541A
Lock:Acquired 7 0X06A08A00010000000
Lock:Acquired 4 0X07A0D4AB884E10E0D057643E
Lock:Released 0X07A0D4AB884E10E0D057643E
Lock:Acquired 4 0X07A0D4AB884E10F0B530D886
Lock:Acquired 7 0X06A08A00010000000
Lock:Acquired 4 0X07A0D4AB884E10F0B530D886
Lock:Acquired 5 0X07A0D4AB884E10F0B530D886
Lock:Released 0X07A0D4AB884E10F0B530D886
Lock:Acquired 8 0X06A08A00010000000
Lock:Acquired 7 0X06A08A00010000000
Lock:Released 0X06A08A00010000000
Lock:Released 0X07A0D4AB884E10F0B530D886
Lock:Released 0X07A0D4AB884E10F0B530D886
Lock:Released 0X06A08A00010000000
Lock:Released 0X07A0293FAC4B101F02A67C1D6
Lock:Released 0X06A08900010000000
SQL:BatchCompleted DELETE FROM contract WHERE contract.contractid=31

 

[jgoodman00]

Ok in an attempt to replicate I did the following. All of this was done in query analyzer, using the Northwind DB, & I modified the FK's on ORDER_DETAILS & ORDERS to include a cascade delete:

In 1 connection:

begin tran t1
select * from orders
with (updlock, rowlock)
where customerid = 'alfki'

In a 2nd connection:

begin tran t1

select * from orders where customerid = 'anton'

delete from customers where customerid = 'anton'

select * from orders where customerid = 'anton'

The first select returns matching records.

The second select statement returns no records, indicating the delete was successful.



Can you try this & confirm it works as expected on your machine?

James Goodman MCSE, MCDBA

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

 

[itflash]

Thanks for your suggestion and replies.


This is the outcome of your example:


(7 row(s) affected)

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Northwind', table 'Orders', column 'CustomerID'.
The statement has been terminated.

(7 row(s) affected)



if I change you code to:

begin tran t1
select * from orders where customerid = 'anton'
delete from [b]orders[/b] where customerid = 'anton'
select * from orders where customerid = 'anton'

Then I get 7 rows and then no rows in the preview pane.



Does this provide any further clarifcations?

 

[jgoodman00]

Yes, did you enable Referential Cascading deletes on the Orders & Order_Details tables?
You need to do this otherwise it will return the error you are seeing as it would breach referential integrity between Customers & Orders...

James Goodman MCSE, MCDBA

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

 

[itflash]

Sorry, yes, done as you asked. I did a relationship from "orders" to "order details", but forgot "customers".

When done, it just stays on "Executing Query Batch" until I stop it.

Looks like it is locked.

Is this the same for you?


Thanks
ITflash