Lock/block retained after commit

[Bob Jones]

I am using SQL server 2000 with Uniface (4GL).

I am running a process which commits every 30 seconds or so on one
machine and another user is trying to perform a small update at the
same time on the one of the tables whose data is modified by the first
process.

I accept that the two second process may get blocked at times by the
first but expect that to only happen rarely due to the likelihood of
both processes trying to update the very same row in a large table.
Other users are also on line at the same time performing updates
across the database.

What I have witnessed though was the second process being blocked by
the first (confirmed by looking in enterprise manager) and REMAINING
BLOCKED EVEN AFTER A COMMIT in the first process!!

My big problem is that I have only seen this happen once on a customer
site and have not been able to reproduce it myself to provide any
further information.

The application is set to use row level locking and is also in read
uncommited mode.

Any help or suggestions would be greatly appreciated.

Bob.

[Simon Hayes]

"Bob Jones" <un**********@hotmail.com> wrote in message
news:1b**************************@posting.google.c om...

I am using SQL server 2000 with Uniface (4GL).

I am running a process which commits every 30 seconds or so on one
machine and another user is trying to perform a small update at the
same time on the one of the tables whose data is modified by the first
process.

I accept that the two second process may get blocked at times by the
first but expect that to only happen rarely due to the likelihood of
both processes trying to update the very same row in a large table.
Other users are also on line at the same time performing updates
across the database.

What I have witnessed though was the second process being blocked by
the first (confirmed by looking in enterprise manager) and REMAINING
BLOCKED EVEN AFTER A COMMIT in the first process!!

My big problem is that I have only seen this happen once on a customer
site and have not been able to reproduce it myself to provide any
further information.

The application is set to use row level locking and is also in read
uncommited mode.

Any help or suggestions would be greatly appreciated.

Bob.

I would start by trying to identify what the lock is and on which object -
you can use a combination of sp_lock and sp_who2, but you might find
Erland's tool to be more flexible:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

You could also use a Profiler trace to gather more information about the
processes which are active when the problem occurs. Hopefully that will give
you enough information to narrow down the problem to a specific component or
piece of code. Finally, you should probably make sure that the transaction
really is COMMITted, especially if it's started in a middle-tier or client
component as opposed to a stored procedure - Profiler should be able to help
there, and of course DBCC OPENTRAN.

Simon

[Gang He [MSFT]]

I am pretty sure the cause for the blocking is that the transaction in the
first process did NOT really commit. If this shows up again, you can use
the following cmd to find out who is blocking the second process and whether
it has any active transaction associated with.

select spid, blocked, open_tran from sysprocesses

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Jones" <un**********@hotmail.com> wrote in message
news:1b**************************@posting.google.c om...

I am using SQL server 2000 with Uniface (4GL).

I am running a process which commits every 30 seconds or so on one
machine and another user is trying to perform a small update at the
same time on the one of the tables whose data is modified by the first
process.

I accept that the two second process may get blocked at times by the
first but expect that to only happen rarely due to the likelihood of
both processes trying to update the very same row in a large table.
Other users are also on line at the same time performing updates
across the database.

What I have witnessed though was the second process being blocked by
the first (confirmed by looking in enterprise manager) and REMAINING
BLOCKED EVEN AFTER A COMMIT in the first process!!

My big problem is that I have only seen this happen once on a customer
site and have not been able to reproduce it myself to provide any
further information.

The application is set to use row level locking and is also in read
uncommited mode.

Any help or suggestions would be greatly appreciated.

Bob.

[Dan Guzman]

The most likely cause is that either the COMMIT is not being executed as
expected or more than one BEGIN TRAN was executed before the COMMIT. Since
this occurs with only one of your customers, it may be that data differences
cause a different code path to be executed.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bob Jones" <un**********@hotmail.com> wrote in message
news:1b**************************@posting.google.c om...

I am using SQL server 2000 with Uniface (4GL).

I am running a process which commits every 30 seconds or so on one
machine and another user is trying to perform a small update at the
same time on the one of the tables whose data is modified by the first
process.

I accept that the two second process may get blocked at times by the
first but expect that to only happen rarely due to the likelihood of
both processes trying to update the very same row in a large table.
Other users are also on line at the same time performing updates
across the database.

What I have witnessed though was the second process being blocked by
the first (confirmed by looking in enterprise manager) and REMAINING
BLOCKED EVEN AFTER A COMMIT in the first process!!

My big problem is that I have only seen this happen once on a customer
site and have not been able to reproduce it myself to provide any
further information.

The application is set to use row level locking and is also in read
uncommited mode.

Any help or suggestions would be greatly appreciated.

Bob.