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