DELETE during the transaction WAIT forever

[bborissov]

I am not sure if this is the right forum to ask, but because I am not an administrator and want to handle this from my program I'll ask here
So the question:

In simple:
I crated two connections to the server bot opened transaction to one table and insert records in it. BEFORE COMMIT (or ROLLBACK) that transaction I need to delete record. On one server I have no problems at all, on other when the script goes to DELETE statement it start waiting till other connection close the transaction. I tried different ISOLATION LEVELS but to no avail. Both servers using MSDE SP3 and both are installed with default options. In short (and code

-- Connection 1                    -- Connection 2
BEGIN TRANSACTION                  BEGIN TRANSACTION
INSERT INTO MyTable VALUES (111)   INSERT INTO MyTable VALUES (222)

-- The row below WAIT FOREVER on server 1
-- on other server no problems.
DELETE FROM MyTable WHER Fld1 = 111

I need to know what could cause that problem and is it possible to resolve it from the program?

TIA


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[onpnt]

The first thing that comes to mind if a deadlock. Do you have profiler to check this?

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[bborissov]

Thank you,
Table has no constraints, no triggers that could cause that.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[onpnt]

You're still creating a perfect deadlock scenario. The only way around it I know of is to put a COMMIT after the transaction. I'm not sure that will be the best course of action either though. Profiler would let us know exactly which point the lock is happening.

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[bborissov]

OK, but why the3 same thing works on other server?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

No deadlocks, when I run profiler and the job goes to DELETE row in Profiler I get:

Lock:Acquired
Lock:Released

Lock:Acquired
Lock:Released

Lock:Acquired
Lock:Released
....

BUT ClientProcessId = 0 and SPID is not the same as SPID of DELETE row.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP