Stored procedures vs standard select update, avoid locks

In order to retrieve an ID, I first do a select and then an update, in two consequent queries.

The problem is that I am having problems with locked rows. I've read that putting both this statements, Select and Update in one stored procedure it helps with the locks. Is this true?

The queries I run are:

select counter 
from dba.counter_list 
where table_name = :TableName

update dba.counter_list 
set counter = :NewCounter 
where table_name = :TableName

The problem is that it can happen that multiple users are selecting the same row and also possible that they update the same row.

Solution

Assumptions:

you're using Sybase ASE
your select returns a single value for counter
you may want the old counter value for some purpose other than performing the update

Consider the following update statement which should eliminate any race conditions that may occur with multiple users running your select/update logic concurrently:

declare @counter int            -- change to the appropriate datatype

update  dba.counter_list
set     @counter = counter,     -- grab current value
        counter  = :NewCounter  -- set to new value
where   table_name = :TableName

select  @counter                -- send previous counter value to client

the update obtains an exclusive lock on the desired row (or page/table depending on table design and locking scheme)
with an exclusive lock in place you're able to retrieve the current value and set the new value with a single statement

Whether you submit the above via a SQL batch or a stored proc call is up to you and your DBA to decide ...

if statement cache is disabled, a SQL batch will need to be compiled each time it's submitted to the dataserver
if statement cache is enabled, and you submit this SQL batch on a regular basis then there's a chance the previous query plan is still in statement/procedure cache thus eliminating the (costly) compilation step
if a copy of previous stored proc (query) plan is not in procedure cache then you'll incur the (costly) compilation step when loading a (proc) query plan into procedure cahe
a stored proc is typically easier to replace in the event of a syntax/logic/performance issue (as opposed to editing, and possibly compiling, a front-end application)
... add your (least) favorite argument for SQL batch vs stored proc (vs prepared statement?) vs ??? ...