Let me correct the next to last sentence in the 5th paragraph of my answer: If you are using
 repeatable read, then I would expect to see escalation if a large result set is returned by
 the select.

Sorry for confusing the two ... it's pretty key to get Transaction Isolation Level in the an
swer right in order to understand what I'm talking about

From: Steve Howard (PFE)
Sent: Thursday, May 01, 2008 6:25 PM
To: 'Shashank Singh'; Perl-Win32-Database@[...].com
Subject: RE: update query fails as table gets locked by select query (sql blocking issue)

This doesn't sound unusual, but check the transaction isolation level anyway. One part of yo
ur problem description makes me think you MIGHT not be using Read Committed.

When you are doing a select with read committed transaction isolation level, SQL takes a sha
red lock on the row (if the optimizer deems row level locking appropriate). If it needs to s
elect another record (which sounds like the case in your query), it then takes a shared lock
 on the second row, and AFTER this lock is acquired, it releases the shared lock on the firs
t row. If you are using Repeatable Read, then the shared lock will be held until the end of 
your transaction. So you have this going on in your connection where you are doing the selec
t.

You then open a new connection. This connection has a new SPID which is not associated in an
y way with the SPID where your select is being handled. You issue an update. SQL first issue
s an update lock to evaluate a row to see if it is what needs to be updated. An update lock 
is compatible with a shared lock, so the evaluation can be successfully made. When SQL deter
mines that this row needs to be modified, it then converts the update lock to an exclusive l
ock. However; a shared lock is not compatible with an exclusive lock, so as long as your fir
st connection maintains a shared lock, the update statement will be blocked, and cannot comp
lete the update. If you check sp_who2 on your SQL server while you are in this state, you wi
ll see the second SPID blocked by the first SPID.

As for the threshold, here is where I am not sure you are using Read Committed:

When SQL begins a query, the optimizer should estimate the optimal locking granularity. Most
 times, this is row lock, or page lock. However; when a threshold of 5000 locks for a single
 object is reached (or a certain percentage of available lock memory is used), SQL will atte
mpt to escalate the lock. Locks are ALWAYS escalated to table locks. The reason I suspect th
at you might not be using Read Committed is because the locks should roll off as I described
 in the second paragraph if you are using read committed. If the locks roll off, then your s
elect should not escalate its shared locks to a table lock. If you are using read committed,
 then I would expect to see escalation if a large result set is returned. I would also expec
t to see escalation on the update if a large number of rows are updated.

When you used a "nolock" hint on your select query, you told SQL to behave like "read uncomm
itted." This means the select will not take any shared locks, and will not respect any locks
 from any other SPIDs. In this case, you do not see the blocking issue, but you run the risk
 of reading transactionally inconsistent data. This is not normally recommended - especially
 if you are using that data for logic in your update.

What might be a better solution is if your select either retrieves all the data first, and y
ou finish that statement, then use the data you retrieved into a Perl data structure to issu
e your updates, or if there is not enough memory on the client side, then select the data in
to a temp table and then use the data from the temp table to issue your updates. If it is po
ssible to issue a single update maybe with a where clause that updates the rows you are curr
ently selecting (thus reducing the process to one statement) that would be the best solution
. You may use an update based on a join to accomplish the last one These are just a couple o
f suggestions without really knowing what you are trying to accomplish, but see if there is 
a way to do one of these as it will get past the blocking you are seeing..

From: perl-win32-database-bounces@[...].com [mailto:perl-win32-database-bounces@[...].com] O
n Behalf Of Shashank Singh
Sent: Thursday, May 01, 2008 5:39 PM
To: Perl-Win32-Database@[...].com
Subject: update query fails as table gets locked by select query (sql blocking issue)

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

* 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 qu
ery.


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 qu
ery 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