Question

update query fails as table gets locked by select query (sql blocking issue).

Perl, SQL Server 2000

I have a perl script which updates some table records in sqlserver 2000, the flow is like below:

* A select query
This select query selects IDs(with other records too)

* An update query
This update query updates a bit column from 0 to 1 for the IDs retrieved from above select query.

The problem I am facing is: the update query can not update the records because the table is being locked by the first select query( only when select query returns records more than some threshold, let say if it return 18 records update runs fine but if select returns more than 18 records, update fails as table gets locked by select query.

Please help me out.

Thanks

This is probably because the lock on the table is escalating. There are a few ways to prevent this.

1. Have both queries run in the same session.
2. Close the session to the database that the select statement is using before you start running update statements.
3. Use a stored procedure to return the values as well as flip the value in the bit column, so that you only have to make a single call.
4. Add the NOLOCK hint to your SELECT statement so that it won't lock the records. You may get a problem with invalid data being shown as if anyone else has a lock on a record, that lock will not be honored.

SELECT *
 FROM Table WITH (NOLOCK)
 WHERE Something = SomethingElse