>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 <s>
>So the question:
>
>In simple:
>I created two connections to the server both 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
><pre>
>
>When I run SQL Profiler to check dead locks it doesn't shows any, it just show:
><pre>
>Lock:Acquired
>Lock:Released
>
>Lock:Acquired
>Lock:Released
>
>Lock:Acquired
>Lock:Released
>....
>

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

Hi Borislav,

I guess that:
Fld1 it is not indexed
and
the sequence that wait forever is this:

conn1: INSERT INTO MyTable VALUES (111)
conn2: INSERT INTO MyTable VALUES (222)
conn1: DELETE FROM MyTable WHER Fld1 = 111 -- conn2 lock this on the pk index

Solution 1:

conn1: INSERT INTO MyTable VALUES (111)
conn2: INSERT INTO MyTable VALUES (222)
conn1: DELETE T FROM MyTable T (READPAST) WHERE Fld1 = 111

Solution 2:
Create an index for the Fld1 field.

Fabio