Hey Steve,

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

>    This doesn't sound unusual, but check the transaction isolation level
>  anyway. One part of your 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 shared lock on the row (if the optimizer deems row level
>  locking appropriate). If it needs to select 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 first
>  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 select.
> 
>   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 any way with the SPID where your select is being handled.
>  You issue an update. SQL first issues 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 determines
>  that this row needs to be modified, it then converts the update lock to an
>  exclusive lock. However; a shared lock is not compatible with an exclusive
>  lock, so as long as your first connection maintains a shared lock, the
>  update statement will be blocked, and cannot complete the update. If you
>  check sp_who2 on your SQL server while you are in this state, you will 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 query).
> 
>  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 attempt to escalate
>  the lock. Locks are ALWAYS escalated to table locks. The reason I suspect
>  that 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 select 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 expect 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 exclusive lock. Just to tell you if my select query returns
>  18 rows, there is no blocking issue but if select query returns more than 18
>  rows, the blocking issue comes into picture. I feel the 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 after some time shared lock due to select
>  statement goes away. This way we can also say that select query is running
>  in Read Commited isolation level otherwise shared lock should persist till
>  the end of SELECT transaction, which is not. Buy what makes a difference
>  between processing 18 & 19 records, ideally if it is working fine for 18
>  records then it should also work fine for 19 records, what do you say?
> 
>  When you used a "nolock" hint on your select query, you told SQL to behave
>  like "read uncommitted." 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 you finish that statement, then use the data you retrieved
>  into a Perl data structure to issue your updates, or if there is not enough
>  memory on the client side, then select the data into a temp table and then
>  use the data from the temp table to issue your updates. If it is possible to
>  issue a single update maybe with a where clause that updates the rows you
>  are currently 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 of 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 because the select query would have never
>  crossed the threshold value of recordsets). And moreover  i am very keen
>  to know the root of this blocking issue as i have spent lots of time in
>  troubleshooting.
> 
    Please let me know if you need some more inputs to troubleshoot the root
cause. If required 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] *On 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 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
>