Problem due to locking in MSSQLServer 2000

Insert or update statements seems to be locking entire
page or table rather than locking
the corresponding row to be inserted or updated.
lets assume the table with 3 rows.
scenario 1)
In transaction A, I'm updating the 3'rd row. and in
another transaction B I'm reading
row 1.
Transaction B seems to be waiting for transaction A to
finish before returning the select results.
Scenario 2)
In transaction A, I'm inserting new row (4'th row) and in
another transaction B I'm reading
row 1.
here as well trasaction B does not return the row 1 unless
transaction A is complete.
Select operation is blocked due to insert.
Ideally in both the scenarios , read operation should have
returned the results without waiting
for update/insert to finish. As the read is being done on
different rows than that of being updated
or inserted.
I have tried both the insert/update as well as select
queries with all the possible locking hints
such as ROWLOCK, READCOMMITED, UPDLOCK etc...
The only way select query returns the row without blocking
is by using the NOLOCK locking hint. But then this is
not the proper solution as it gives us the dirty read.
Please suggest me any solution or workaround for above
issue.Adding to Tony's comments (the DDL/DML will be useful), you might find the
problem is that it's the index page that's locked during the update/insert
which is blocking the select. How many rows are in the table we're
discussing?
Depending on the WHERE clause and the optimizer query plans, sometimes row
locks are NOT released until the entire statement has finished, and
sometimes these can be escalated to table locks. See
http://www.hanlincrest.com/SQLServerLockEscalation.htm for a description.
I couldn't quite figure it out from your note, but is it the same process
attempting to execute two separate transactions on two SQL Server sessions
and thus blocking itself? If this is the case it would be wise to rethink it
so only one transaction is active from the process ar once.
Kind Regards, Howard
"Sangram Deshmukh" <sangram@.savvion.com> wrote in message
news:0c2e01c34ad9$841875a0$a301280a@.phx.gbl...
> Insert or update statements seems to be locking entire
> page or table rather than locking
> the corresponding row to be inserted or updated.
> lets assume the table with 3 rows.
> scenario 1)
> In transaction A, I'm updating the 3'rd row. and in
> another transaction B I'm reading
> row 1.
> Transaction B seems to be waiting for transaction A to
> finish before returning the select results.
>
> Scenario 2)
> In transaction A, I'm inserting new row (4'th row) and in
> another transaction B I'm reading
> row 1.
> here as well trasaction B does not return the row 1 unless
> transaction A is complete.
> Select operation is blocked due to insert.
>
> Ideally in both the scenarios , read operation should have
> returned the results without waiting
> for update/insert to finish. As the read is being done on
> different rows than that of being updated
> or inserted.
> I have tried both the insert/update as well as select
> queries with all the possible locking hints
> such as ROWLOCK, READCOMMITED, UPDLOCK etc...
> The only way select query returns the row without blocking
> is by using the NOLOCK locking hint. But then this is
> not the proper solution as it gives us the dirty read.
> Please suggest me any solution or workaround for above
> issue.