<div>
First, I am curious why you are getting table locks at all. &nbsp;Normally you will get page locks unless either the table is very small and/or the number of pages being touched is so large that the system escalates from page to table locking. &nbsp;A third reason is if you have not updated statistics recently or since the table(s) were loaded and the optimizer thinks they are empty. &nbsp;Do the updates touch more than one row at a time? &nbsp;Another possibility yet is that you WHERE clause does not use an index at all forcing the UPDATE to do a full table scan.<br />
<br />
Once you get the page/table locking thing straightened out, you will find the code for the third update is redundant. &nbsp;An update that does not actually touch any rows will not lock anything so you can remove the IF EXISTS logic as it is already expressed in the WHERE clause of the UPDATE.<br />
<br />
You do not mention whether or not you are using an explicit BEGIN TRANS / COMMIT around these statements. &nbsp;If they need to happen as a unit in order to maintain relational integrity, you should use explicit transaction control so a failure causes all the updates to roll back together.<br />
<br />
Is the stored procedure and/or the session setup for chained or unchained mode?<br />
<br />
Finally, an experiment you can do is to change the locking scheme on one or more of these tables to &quot;row locking&quot;. &nbsp;I would not do that unless you cannot convince the databases to lock at the page level. &nbsp;You may also want to check the server options to see what the lock escalation threshold is set to but I doubt that is the problem.<br />
<br />
Regards,<br />
Bill<br />

</div>


<div>
Yes, indeed they are page locks although they could have been tablel ocks since this was a very small set of tables. My bad.<br />
<br />
The third update, although not expressed well, will cause a redundant update if some cases and therefore temporarily lock the this table that is a component of other heavily used queries. i was just trying to reduce the contention without recoding any more than I had to, but perhaps I should recode for that case.<br />
<br />
The transaction is being created within a higher level-language, should have mentioned that. So I guess the proc is in unchained mode.<br />
<br />
I believe the tables will be small in production. I will let the DBA know of the row locking idea.<br />
<br />
My overall conern is that table3 may cause blocking for the queries that other users will be issuing.<br />
<br />

</div>


<div>
Very complete coverage of the question
</div>


<div>
<div class="content wysiwyg-content">
I have a stored procedure that performs some updates and locks the tables it updates. One of the tables it need not always lock.<br />
UPDATE table1 SET...WHERE...<br />
UPDATE table2 SET...WHERE...<br />
IF EXISTS(select * from table3 where...)<br />
&nbsp; UPDATE table3 SET ... WHERE...<br />
<br />
Will SYBASE always lock for update table3 based upon the stored execution plan or will the lock happen conditionally based upon what was executed?<br />
<br />
Regards to all SYBASE lovers...facetiously<br />
Charlie
</div>
</div>


I have a stored procedure that performs some updates and locks the tables it updates. One of the tables it need not always lock.
UPDATE table1 SET...WHERE...
UPDATE table2 SET...WHERE...
IF EXISTS(select * from table3 where...)
  UPDATE table3 SET ... WHERE...

Will SYBASE always lock for update table3 based upon the stored execution plan or will the lock happen conditionally based upon what was executed?

Regards to all SYBASE lovers...facetiously
Charlie

SYBASE table locking within a transaction

Sybase, a subsidiary of SAP, builds a client/server relational database management system. Products include Adaptive Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase Unwired Platform (SUP) for mobile applications, Afaria for enterprise mobile device management and IQ for data warehouse and big data applications.