I have a perl script which updates some records in sqlserver 2000,
the queries are like below:

* A select query
This select query selects records that needs to be updated.
* An update query
This update query updates a bit column(from 0 to 1) for the IDs retrieved in
above select query.


the pseudo code is like below:

Open SQL Connection for select ;

Execute SELECT query;

WHILE( ALL IDs not processed)
{

Open SQL Connection for update ;

 Execute UPDATE query;

 Close SQL Connection for update ;

}

Close SQL Connection for select;




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. The weired
thing i could see is that when select query returns records more than some
threshold size, the update starts failing for example let say if select
returns records of  X Kbytes update runs fine but if select returns more
than X+ sized records, update fails as table gets locked by select query.

If I add with nolock in select query tool runs fine in every scenario.

 Is it a issue of hash mapping of select recordsets in memory?


Please help me out.




Snapshot of perl script is below:





Thanks