I'll help with troubleshooting, but let's take it offline as the blocking is occurring in SQ
L rather than in Perl. We can post the final resolution back into the list if you'd like.


From: perl-win32-database-bounces@[...].com [mailto:perl-win32-database-bounces@[...].com] O
n Behalf Of Shashank Singh
Sent: Friday, May 02, 2008 3:00 AM
To: perl-win32-database@[...].com
Subject: Re: update query fails as table gets locked by select query (sql blocking issue)

Hey Steve,

Thanks a ton to help me out with this unusual problem, please find some further inputs inlin
e:
On Thu, May 1, 2008 at 6:24 PM, Steve Howard (PFE) <sthoward@[...].com<mailto:sthoward@[...]
.com> > wrote:

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.

 I didn't set any transaction level in my queries so it should be using the default one i.e.
 Read Commited, anyway i tried to set Read Commited explicitely still no luck :(

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.

 Yes you are right the second SPID(update query) is being blocked by the first SPID(select q
uery).

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.

 If i execute SP_LOCK, the select query helds a shared lock and update query helds an exclus
ive lock. Just to tell you if my select query returns 18 rows, there is no blocking issue bu
t if select query returns more than 18 rows, the blocking issue comes into picture. I feel t
he problem is related with the duration of shared lock helds on the table which needs to be 
updated, because if i use retry logic in my code for update query execution then after some 
retries update gets succeeded for records more than the threshold too. It suggests that afte
r some time shared lock due to select statement goes away. This way we can also say that sel
ect query is running in Read Commited isolation level otherwise shared lock should persist t
ill the end of SELECT transaction, which is not. Buy what makes a difference between process
ing 18 & 19 records, ideally if it is working fine for 18 records then it should also work f
ine for 19 records, what do you say?

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.

 Yes I can not use "nolock" hint as i don't want dirty reads.

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..

 I agree that if i implement your above suggesstions this problem will be resolved. But the 
thing is this application has been running since last 2-3 years without issues ( maybe becau
se the select query would have never crossed the threshold value of recordsets). And moreove
r  i am very keen to know the root of this blocking issue as i have spent lots of time in tr
oubleshooting.
    Please let me know if you need some more inputs to troubleshoot the root cause. If requi
red I can also share the complete perl script.

Thanks again to help me out.

--Shashank




From: perl-win32-database-bounces@[...].com<mailto:perl-win32-database-bounces@[...].com>  [m
ailto: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<mailto: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