Newbie: Concurrent query and update

> On 2008-08-28 07:59:58 +1000, "Mark A. Parsons"
> said:
>
> I don't can postings. I could care less if certain individuals wish to
> make an arse of themselves. If those individuals later want their
> postings canned then they should consider not submitting cannable posts
> in the first place.

No idea who/what you are talking about. I have not asked for my posts
to be "canned".

Be careful. It may not be me who ends up making an whatever of themselves.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

Derek Asirvadem wrote:
> I suppose it is beyond your imagination that
> (a) there could be known problems
> (b) there are known solutions
> (c) that can be eliminated by the implemenation of standards
> (d) it is you who is making the same mistake every year

Fair enough, let's go back to your original post in which you state:

"the holdlock acquires a Shared lock and is standard for Optimistic Locking."

And then you immediately post the following snippet:

==============================
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
==============================

From your posting it appears that this snippet is suppose to represent some of the coding you would implement for
optimistic locking.

Most folks familiar with how optimistic locking works realize that the update statement requires a where clause that
validates the set clause, ie, that the conditions under which the set value were obtained still apply. This would
suggest that the code snippet requires an additional where clause on the update:

==============================
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"

and seq_num = (@seq_num - 1) -- validation that SEQ_NUM has not been
-- updated since @seq_num was populated

-- more code
commit transaction mytran
==============================

Failure to include the validating where clause, when used in conjunction with a high-concurrency environment, can cause
deadlocks (per my example post) and/or duplicate PK violations (eg, 2 processes attempt to insert the same @seq_num into
BMF_BATCH where BMF_BATCH.Id is unique).

If you accidentally left off the validating where clause then say so, no biggie. (And make sure you don't make the same
mistake next year.)

If you're claiming that you do not need the additional validating where clause then you've either made an assumption
that there will never be 2+ processes running this same code snippet at the same time (in which case you should state
said assumption), or you don't understand how optimistic locking is implemented.

Care to address the technical issues or do you wish to go off on a tangent about how I'm 'attacking' your posts?

> Your collusion/impersonation is demonstrated.

I'll give you this ... you've got one active imagination.

My guess is that the anonymous folks don't have thick enough skin to put up with your verbal attacks, demeaning
'explanations', threatening emails and phone calls.

I have no problems putting my name on my posts.

> Don't forget, your posts and attacks on me here, have been summarily
> deleted as well.

1 - technical posts aren't deleted

2 - offending non-technical posts are deleted; also deleted may be other posts which by themselves are not offending but
which contain bits-n-pieces/references to an offending post which has been deleted

> On 2008-08-28 10:58:32 +1000, "Mark A. Parsons"
> said:
>
>

Your post/example is convoluted; and the evidence of one point against
another, taken out of context, does not stand. It is no use my
responding in detail because I will only continue the convolution.

I have already posted a question on this topic (but not this exact
branch) which provides a clean start and a challenege for you to
proceed with, If you can keep it civil and see it to completion, so
that we do not have to the same confrontation without resolution every
year. Why don't you take that up. Nice clean start and we can both be
careful about each response, all the way through to resolution.

Besides, there are several statements in your post that I disagree with
.... and while I made whatever statemenst I made which are on record,
your intrepretation/application is not correct ... I will respond to a
couple of items to show you what I mean.

> "the holdlock acquires a Shared lock and is standard for Optimistic Locking."

Had I known you would mount the annual "technical" attack, I would have
been more precise. I have stated later, people do not have 15
different templates; they have one template which implements all the
standards they have chosen. Now that we have the new context (your
nit-picking), let me now state (keeping the original context in mind
and without changing the meaning of my statement):

> ... the holdlock acquires a Shared lock

• The Holdlock example acquires a Shared lock which is superior to
acquiring an Update lock

> ... is standard ...

• Well now, people do not have 15 different templates; they have one
template which implements all the standards they have chosen
• the standard template will include an implementation to take care of
all the following standards which are chosen: small tight transactions,
Optimistic Locking, avoidance of Phantoms and Lost Updates, High
Concurrency, reduction of (a) locks held and (b) duration locks held;
disallowing locks held for uncontrolled durations, etc [not the
exhaustive list]
• where one has implemented a reasonable subset of these standards, the
Holdlock example is demanded by standard, •Â*and the Update-First
example is disallowed.

Of course, it must be emphasised that anyone with reasonable knowledge
of said standards would have understood what I was communicating
originally, and thus would not have picked nits. Conversely one who
picks my original response to bits does not reasonably understand the
issues, the possible solutions, and the standards.

> Failure to include the validating where clause, when used in
> conjunction with a high-concurrency environment, can cause deadlocks
> (per my example post)

definitely not. The whole single standard will prevent that (not just
the example code segment)

> and/or duplicate PK violations (eg, 2 processes attempt to insert the
> same @seq_num into BMF_BATCH where BMF_BATCH.Id is unique).

definitely not in my universe.

> If you accidentally left off the validating where clause then say so,
> no biggie. (And make sure you don't make the same mistake next year.)

Here is an example of you reading too much into my statements (a) I was
answering OP, not providing a complete dissertation on the problem (b)
nit-pickers can always find holes when statements are taken out of
context.

I have no problem admitting mistakes. I did not leave it off; it
wasn't on; I did not add it. I did not know you were going to argue
about it later, otherwise I would have put it in, which would have
required a bigger more meaningful example.

> If you're claiming that you do not need the additional validating where
> clause then you've either made an assumption that there will never be
> 2+ processes running this same code snippet at the same time (in which
> case you should state said assumption), or you don't understand how
> optimistic locking is implemented.

Excellent logic. Yes, of course you need an additional validating
where clause. But no, I completely disagree with your example of one.

> Care to address the technical issues or do you wish to go off on a
> tangent about how I'm 'attacking' your posts?

I have responded directly here. Let's close this convoluted
thread/branch, and please take up the fresh start on the other
thread/branch.

It is not a tangent, it is fact on record.

>> Your collusion/impersonation is demonstrated.
>
> I'll give you this ... you've got one active imagination.

Let's see what the IP addresses reveal.

> My guess is that the anonymous folks don't have thick enough skin to
> put up with your verbal attacks, demeaning 'explanations', threatening
> emails and phone calls.

Maybe they are sending you the threatening emails and phone calls,
because I certainly have not. Or maybe you trying to paint a picture
that is false. Go on then, send me any of the alleged emails.

>>> ... then we can ban individuals just like ISUG does.
>
>> Don't forget, your posts and attacks on me here, have been summarily
>> deleted as well.
>
> 1 - technical posts aren't deleted
>
> 2 - offending non-technical posts are deleted; also deleted may be
> other posts which by themselves are not offending but which contain
> bits-n-pieces/references to an offending post which has been deleted

You are side-stepping this point as well. You have attacked me many
times in the past, pure ad hominem attacks, sometimes cloaked in the
mantle of a technical attack, and those attacking posts have been
summarily deleted.

I do not bother complaining about "technical" attacks from people who
are intimidated by me, or who demonstrate that they are clueless about
the problem, the solution, and the standard.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

> On 2008-08-28 09:21:22 +1000, Derek Asirvadem
> said:

Come on, Mark A Parsons.

No suggestion that you are an inexperienced coder, but a nice clean
challenge without convolution or the opportunity to take things out of
context is waiting for you here (as per other post on this topic,
separate branch). Pure technical. Popsickle is getting warm and
runny, will that be one or two ? Happy to provide dissertations and
massive examples if you wish. All we need is two of your one-liners to
take it to the next step, where we can get into the meat of Holdlock vs
Update First; Optimistic Locking; and all that really important
technical stuff that you are so good at picking out the flaws in, so we
can resolve this annual confrontation once and for all. I promise I
will concede every point in writing, even the nit-picky ones. Green
field, blue sky awaits. Aren't you up for taking the challenge you made
to me just a little further ?

Written withdrawal would be gentlemanly; silence will be very un (Just
like posting in someone else's name), and a clear concession of defeat.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

> On 2008-08-28 07:56:57 +1000, "Mark A. Parsons"
> said:
>
> ... then we can ban individuals just like ISUG does.

ISUG has turned out to be a fascist fanclub and soap box/opera. They
have had the same opera playing there, the Mutt & Jeff Show, for many
years. No one else got a word in edgewise. ISUG Board did nothing to
stop it or change it for years. People complained bitterly for years.
The filth, defamation, lies that went on there was shocking; and it was
not stopped. I received heaps of fan mail supporting me in confronting
the bullies, and in Jason's words, I am "morally justified" in doing
so, particularly in light of the fact that the "authorities" were
impotent and anarchy had set in:

> Dear Mr. Asirvadem,
>
> We didn't want to fan the flames once again sending this post over ISUG site.
>
> There are many ridged and selfpromoted big faces*and braggarts on ISUG
> site who behave just as if they owned the site (Talebzadeh & Co). They
> are so prosaic and pesky that we all would be happy to get rid of them.
> Given that they know very well who you are, that you are a man of
> competence, they*look at*you just as a threat to their half-baked
> authorities.
>
> Don't surrender! There are many of us who like and appreciate*your
> posts over the newsgroups*because of*their technical merrit, not to say
> excellence. We encourage you to take your part*in ISUG's
> discussions*and by posting your own threads*on various*subjects and
> topics*with regard to ASE performance (disks, controllers,*partitions,
> raw devices, segments, tempdb, caches, memory etc).*
>
> Sincerely

[Thankyou, glad to be of service.]

Of course, the inevitable happened when Mich attacked me for the
umpteenth time. Since ISUG had written long dissertations on exactly
how they were not responsible for anything, not liable for anything,
not able to do anything, I confronted the bullies once again. I have
done the membership a service by getting the Mutt banned, and I am
unashamed of being banned for that. ISUG does not even have the
honesty or courtesy to identify what specific guidleine I allegedly
breached. Since Mutt is not pontificating about trivialities, Jeff has
no one to counter trivialities with, so the membership is spared the
whole soap opera. Thankyou God.

The membership is not being served, as the bullies shout down anyone
else. There are some great Enhancement Requests (admittedly a few are
mine) that get squashed before discussion takes place. Same as with
you, lots of shouting and 'Technical" opinions from people who, by
definition, do not have that specific experience or a clue about the
subject. Sybase customers and the membership lose. We have to "sell"
the ERs to the "engineers". No wonder our market share is shrinking.

They do not have moderation, they have de facto undeclared censorship.
Anything untoward about their protected few operatics gets canned. As
you evidently know, bullies are cowards who need their special
protected position to attack from, and they fight dirty. They cannot
come out in the open and fight fairly and squarely.

Being the big protected fish in a small pond is one thing. Mich posted
once in the big pond, and ran away screaming in agony at the response.

Nothing changes if nothing changes. In a month when the ban is over,
it will be back to the operatics, the shouting down of competents, the
mental masturbation of trivialites, the censorship of anything
threatening. It is not a website, it is the soapbox for the protected
few.

BTW, the enclosed email might give you an idea as to why I do not get
offended when the clueless attack me "technically". It is a waste of
time to argue with such folks, because they themselves have proven what
they are; no use "selling" high speed train travel to people who are
firmly attached to their horse and cart, who have never been on a slow
train, and who identify me as the devil. I get enough validation from
the people who really count, the attacks from people who do not, do
nothing for/to me.

You are young and you have a lot of energy. Why don't you consider
taking over ISUG, or at least taking a board position. They
desperately need help.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

On 26 Aug 2008 17:20:02 -0700, Derek Asirvadem
wrote:

Ah yes my bad. I mean to convey that even on reads one could want to
hold locks within the transaction. Here is the pointer for him.

http://infocenter.sybase.com/help/in.../locking23.htm

>> On 2008-08-27 01:31:34 +1000, jtotally_bogus-at-sbcglobal.net (J) said:
>
>> 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.
>
>Ah yes, the Phantom Read problem. I agree that the prescribed method
>is to open a tran and use HOLDLOCK. However, that acquires Shared
>locks, not Exclusive locks.
>--
>Cheers
>Derek
>Senior Sybase DBA / Information Architect
>Copyright © 2008 Software Gems Pty Ltd
>Quality Standards = Zero Maintenance + Zero Surprises
>Performance Standards = Predictability + Scaleability
>

if the seq no is a primary key, then updating it with two
similar values will throw error and the second user may have
to re-update the table.

we have similar setup we are using holdlock but concurrecny
is less so it does not matter much and there is no question
of performance degrade.

thanks
ram
> 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-09-10 23:33:17 +1000, Derek Asirvadem
> said:
>
> Now that we are no longer ignorant of the Transaction requirements, we
> 
must find a solution that complies with them. Â*That's your challenge.
> Â* 

create table Security (
SecurityId unsigned int not null, -- PK
Name char(30) not null,
ASXCode char(6) not null,
...
TimeStamp smalldatetime
)

Client Code - Update Security Details Window
----------
Interaction with user
Uncontrolled duration
No locks allowed
No begin tran
No chained
All validation of intended actions [1]
select ... @TimeStamp = TimeStamp from Security where SecurityId = @SecurityId
Any sequence
"save" button execs a stored proc, the Transaction, on the server

Server Code - SecurityUpdate_tr
----------
No interaction with user
Controlled duration
Parms for stored proc include @SecurityId AND @TimeStamp

Prepare_Block
No updates
Begin Tran
All validation of intended actions [2] (if error goto Rollback_Block)
select ... from Security HOLDLOCK where SecurityId = @SecurityId and
TimeStamp = @TimeStamp
set @err = @@error, @rows = @@rowcount
if @err != 0 goto Rollback_Block
if @rows !=1 begin set @err=20002 goto Rollback_Block end
Prescribed Access Sequence

Update_Block
All updates
Prescribed Access Sequence
Commit Tran
Return (0)

Rollback_Block
raiserror @err -- Error 20002 = Data has changed between retrieval and
transaction execution
rollback tran
Return (1)

Validation blocks (1) and (2) are not redundant; both are essential.
(1) validates the user input at the time, during uncontrolled
interaction; data keeps changing. (2) validates the intended action of
the Transaction immediately before starting same.

It is called Optimistic Locking as the approach is optimistic, that the
data will not change between (user) retrieval and transaction
execution. The TimeStamp on the row has many other uses.
Smalldatetime provides granularity to one minute, in 4 bytes per row;
datetime to 3 milliseconds in 8 bytes. Choose carefully.

This struct prevents Lost Updates (Scenario II) as well.

Therefore, the posts by Mark A Parsons alleging to understand
Optimistic Locking, causing deadlocks, etc, in fact understands
neither. Optimistic Locking reduces blocking locks and deadlocks.
Holding locks from the initial user interaction is simply not allowed
by standard, and it WILL cause blocking locks for an uncontrolled
duration (I called it "pessimistic locking" but that is not a standard
term, just a contrast). Great set up for locking up the system; the
spid getting killed or cancelling and locks held forever. Every
teenager with a box of match heads want to split the Atom.
Book D1 is still in print.
--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2008 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability