Stored procedures vs standard select update, avoid locks

Question

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.

Please post your existing code and explain your exact problem with locks. — PM 77-1, Sep 26 '17 at 16:17
Locks are **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Sep 26 '17 at 16:20
**SQL** isn't a database - it's a query language. What **concrete RDBMS** are you using?? Oracle? Mysql? Postgresql? Firebird? Something else? — marc_s, Sep 26 '17 at 16:26
There are (at least) 4 different RDBMS products under the 'Sybase' name; which product are you using? ASE? SQLAnywhere? IQ? Advantage? — markp-fuso, Sep 26 '17 at 16:52
Putting them in one *transaction* would affect locking. I'm not aware that putting them in a stored procedure would have much effect. — Gordon Linoff, Sep 26 '17 at 16:58

score 0 · Answer 1 · answered Sep 26 '17 at 17:10

Is the table counter_list accessed by multiple clients concurrently ?

The best practices for OLTP is to call a stored procedure that will perform the update logic in one transaction.

Check that the table dba.counter_list has an index on column table_name. Check also that it is row level locked.

markp-fuso · Accepted Answer · 2017-09-26T19:58:44.820

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

Thank you very much for your help, very nicely put and explained! — AM AM, Sep 26 '17 at 19:55

Stored procedures vs standard select update, avoid locks

2 Answers2