Newbie: Concurrent query and update

Hi,


Given the code below:


declare @seq_num int

select @seq_num=max(seq_num) from SEQ_NUM where table_name="BMF_BATCH"
select @seq_num=@seq_num+1

begin transaction

select @seq_num=max(seq_num) from SEQ_NUM where table_name="BMF_BATCH"

-- (1)

select @seq_num=@seq_num+1

update SEQ_NUM set seq_num=@seq_num where table_name="BMF_BATCH"

end transaction



Is posible to a concurrent process to get the same "@seq_num" value if
it try to read it in (1)?


Thanks in advance,
Jose Luis.

> On 2008-08-25 16:54:22 +1000, Jose Luis said:
>
> Is posible to a concurrent process to get the same "@seq_num" value if
> it try to read it in (1)?

The idea of a multi-threaded RDBMS is that in concurrent use,
everything is shared, unless you lock it. Two concurrent spids
accessing an unlocked resource will get the same value (by design).

There are a couple of ways to do what you want, either holdlock (read
up on it) or update first. Which method is correct/superior, depends
on your design. The Update first method acquires an Update lock, but
does not allow for Optimistic Locking; the holdlock acquires a Shared
lock and is standard for Optimistic Locking.

Here is the holdlock form:

> begin transaction mytran
> select @seq_num = max(seq_num) + 1 from SEQ_NUM where
> table_name="BMF_BATCH" HOLDLOCK
> -- more code
> insert BMF_BATCH (Id ...) values (@seq_num ...)
> update SEQ_NUM set seq_num = @seq_num where table_name="BMF_BATCH"
> -- more code
> commit transaction mytran

Of course, you need error checking et cetera.


Here is the update form:

> begin transaction mytran
> update SEQ_NUM set seq_num = seq_num + 1 where table_name="BMF_BATCH"
> -- more code
> insert BMF_BATCH (Id ...) values (@seq_num ...)
> -- more code
> commit transaction mytran

BTW This code can be removed:

>> select @seq_num=max(seq_num) from SEQ_NUM where table_name="BMF_BATCH"
>> select @seq_num=@seq_num+1

--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

And BTW, the struct you have for placing the next sequential key value
in a common tabel is not a good idea for high concurrency: you latch on
the one SEQ_NUM table. Even with max rows per page = 1; and row level
locking. Better to latch on each table being inserted into separately,
via

SELECT MAX(PrimaryKey) + 1 FROM TableName

Sybase has done a lot to keep the speed of MAX() up, and it is faster
than the central table.

There are far better ways to increase concurrent inserts (without
latching on one monotonically increasing key value, wherever you get it
from), but that is beyond the original question.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

Assume SEQ_NUM defined as:

=============================
table_name varchar(30)
seq_num int
=============================

Assuming SEQ_NUM contains the record:

table_name seq_num
=============== =========
BMF_BATCH 1





Simulating an environment with a moderate to heavy amount of concurrent activity against SEQ_NUM, consider the proposed
'optimistic locking' code snippet:

================================
declare @seq_num int
begin transaction mytran
select @seq_num = max(seq_num) + 1 from SEQ_NUM HOLDLOCK where
table_name="BMF_BATCH"
-- more code
select @seq_num as new_seq_num
waitfor delay "00:00:02"
update SEQ_NUM set seq_num = @seq_num where table_name="BMF_BATCH"
-- more code
commit transaction mytran
select * from SEQ_NUM
================================



Assume 2 sessions submitting the above code snippet at/near the same time.

NOTE: The 'waitfor delay "00:00:02"' is simply used to allow 2 isql session to be submitted before the first completes,
as could be expected in an environment with concurrent activity against the SEQ_NUM table.


Results:


============= isql session #1

new_seq_num
-----------
2

seq_num table_name
----------- ------------------------------
2 BMF_BATCH

============= isql session #2

new_seq_num
-----------
2

Msg 1205, Level 13, State 1:
Server 'HP2', Line 8:
Your server command (family id #0, process id #20) encountered a deadlock situation. Please re-run your command.

===============================



So while the proposed 'optimistic locking' code snippet does insure only one session gets to keep/use @seq_num = 2, the
overhead of deadlock processing (ie, generating deadlocks, testing for deadlocks, and resubmitting the entire deadlocked
transaction - *possibly many times*) leads to overall degraded performance.



So what about the update-first method:

================================
declare @seq_num int
begin transaction mytran
update SEQ_NUM
set @seq_num = seq_num + 1,
seq_num = seq_num +1
where table_name = 'BMF_BATCH'
-- more code
waitfor delay "00:00:02"
select @seq_num as new_seq_num
-- more code
commit transaction mytran
select * from SEQ_NUM
================================


Results of running 2 copies of the above code snippet at the same time:



============= isql session #1

new_seq_num
-----------
2

seq_num table_name
----------- ------------------------------
2 BMF_BATCH

============= isql session #2

new_seq_num
-----------
3

seq_num table_name
----------- ------------------------------
3 BMF_BATCH

==============================


While the second session would have blocked waiting for the first session to complete, there is none of the overhead for
deadlock processing.

Between the 2 proposed methods, the update-first method provides for better/improved performance due to the elimination
of deadlock processing overhead. This becomes more apparent as the processing requirements of 'more code' increases,
ie, the overhead to resubmit a large volume of 'more code' activity each time it deadlocks becomes quite expensive.


Granted, neither method is conducive to high concurrent activity from a transactional point of view since SEQ_NUM serves
as a single point of contention. Addressing this bottleneck would require some redesign at the database, transaction
and/or process level.

> On 2008-08-26 08:43:01 +1000, "Mark A. Parsons"
> said:

> So while the proposed 'optimistic locking' code snippet does insure
> only one session gets to keep/use @seq_num = 2, the overhead of
> deadlock processing (ie, generating deadlocks, testing for deadlocks,
> and resubmitting the entire deadlocked transaction - *possibly many
> times*) leads to overall degraded performance.

You misunderstand. It is not I who is asking for Optimistic Locking.
Good transaction standards (ACID roperties and their
SQL/OLTP/Client-Server implementation), good design standards (avoid
holding locks for longer than absolutely required), demand Optimistic
Locking. Which means something totally different from the example you
have provided. Your example is in fact "Pessimistic Locking", and it
is the common progression when the coder finds that (a) they should
have SOME locking in their innocent code and (b) they put something
into the existing code to beef it up, without making the full set of
changes demanded (or without knowing) for Optimistic Locking.

The code snippet I provided does not provide an example of Optimistic
Locking; it provides the code snippet that ALLOWS Optimistic Locking (a
design far greater than said code snippet and the subject of formal
education) to be used.

> Granted, neither method is conducive to high concurrent activity from a
> transactional point of view since SEQ_NUM serves as a single point of
> contention. Addressing this bottleneck would require some redesign at
> the database, transaction and/or process level.

I disagree that one code snippet has huge overhead, and the other does
not; all code is checked for locks and deadlocks; waiting is not
executing. Th epicture you paint is not accurate. But the point is
not worthy of argument, because if one uses Optimistic Locking (the
standard as chosen for implementation), one cannot use the Update First
structure.

If one does not implement Optimistic Locking, then the following is
predictable and guaranteed:
• lost updates
•Â*broken transactions
•Â*blocking locks
• deadlocks
It has nothing to do with either one or the other code snippet.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

> The code snippet I provided does not provide an example of Optimistic
> Locking; it provides the code snippet that ALLOWS Optimistic Locking (a
> design far greater than said code snippet and the subject of formal
> education) to be used.

Actually, in case you are gearing up for an argument, the following
needs to be identified. Generally people who have standards do not
have separate standards for this and standards for that, they have a
single transaction template that implements all the standards they have
chosen.

The HOLDLOCK code snippet initially provided, in addition to ALLOWING
for Optimistic Locking, as demanded if that is a chosen implementation
standard, also allows for reduction of Deadlocks by design. Ie. if an
Access Sequence is a chosen implementation standard.

The HOLDLOCK code snippet initially provided, in addition to allowing
for Optimistic Locking, and reducing of Deadlocks by design, also
allows for high concurrency transactions by design. Ie. if one
understands the virtue and logic of prepared transactions, which
results in less overhead overall in a high concurrency environment.
Otherwise coders cause updates and hold locks which need to be rolled
back, something that can be eliminated by design.

Therefore the Holdlock snippet is demanded for a number of design
requirements due to implementation of standards, and the Update-First
snippet is not acceptable for teh same reasons. On the other hand, I
freely concede that if one does not care about standards or concurrency
(which was the concern of OP), then by all means, use Update-First.

The Oracle/MS/Sybase manuals tell the purchaser how to use the product.
They do not instruct the purchaser on database science or high
concurrency transaction design. that is why those subjects require
separate formal education.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

> Generally people who have standards

your standard looks like post bad information and when
someone points to your mistakes you spend lots time tapp
dancing and saying nothing. you just posted 3 posts and
said nothing useful

Thanks all for your help.

BTW. Have any sense a transaction with only "select" statements?

Regards,
Jose Luis.



---------- Forwarded message ----------
From: Jose Luis
Date: Aug 25, 8:54*am
Subject: Newbie: Concurrent query and update
To: sybase.public.ase.general


Hi,

Given the code below:

declare @seq_num int

select @seq_num=max(seq_num) from SEQ_NUM where table_name="BMF_BATCH"
select @seq_num=@seq_num+1

begin transaction

select @seq_num=max(seq_num) from SEQ_NUM where table_name="BMF_BATCH"

-- *(1)

select @seq_num=@seq_num+1

update SEQ_NUM set seq_num=@seq_num where table_name="BMF_BATCH"

end transaction

Is posible to a concurrent process to get the same "@seq_num" value if
it try to read it in (1)?

Thanks in advance,
Jose Luis.

> On 2008-08-26 22:04:55 +1000, Jose Luis said:
>
> BTW. Have any sense a transaction with only "select" statements?

No, not really. There could be a remote case, where you want to
prevent a row from being updated, but then you are holding the row for
some extended period, and breaking all the standards and rules.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

On Tue, 26 Aug 2008 05:04:55 -0700 (PDT), Jose Luis
wrote:

I think there are cases where one needs multiple pieces of information
from more than one select statement to draw conclusions. By holding
exclusive locks on the select statements within a transaction one can
assure that the information is consistent at that point time and act
accordingly.

J

>
>Thanks all for your help.
>
>BTW. Have any sense a transaction with only "select" statements?
>
>Regards,
>Jose Luis.
>
>
>
>---------- Forwarded message ----------
>From: Jose Luis
>Date: Aug 25, 8:54=A0am
>Subject: Newbie: Concurrent query and update
>To: sybase.public.ase.general
>
>
>Hi,
>
>Given the code below:
>
>declare @seq_num int
>
>select @seq_num=3Dmax(seq_num) from SEQ_NUM where table_name=3D"BMF_BATCH"
>select @seq_num=3D-at-seq_num+1
>
>begin transaction
>
>select @seq_num=3Dmax(seq_num) from SEQ_NUM where table_name=3D"BMF_BATCH"
>
>-- =A0(1)
>
>select @seq_num=3D-at-seq_num+1
>
>update SEQ_NUM set seq_num=3D-at-seq_num where table_name=3D"BMF_BATCH"
>
>end transaction
>
>Is posible to a concurrent process to get the same "@seq_num" value if
>it try to read it in (1)?
>
>Thanks in advance,
>Jose Luis.