Re: Avoiding any locks in SQL Servers - read and understand....its magic.

Christopher Browne <cbbrowne_at_acm.org> wrote:
> A long time ago, in a galaxy far, far away, ctcgag_at_hotmail.com wrote:
> > I have very little application code that deals with locking in the
> > first place. I have lots of it that deal with various cannot serialize
> > errors or race conditions, and it sounds like that code has to remain.
>
> The typical example of something that can't vanish entirely is the
> "balance update".

My point was that I don't have much code explicitly dealing with locks *in my applications*. Obviously the database kernel has code dealing with locks, which is why I don't need that code in my applications.

> update financial_account
> set balance = balance - 'some amount'
> where account_id = 'foo';
>
> or
>
> update inventory
> set on_hand = on_hand - 5
> where product_id = 'Grapple Grommets';
>
> If 25 (otherwise valid) transactions come in concurrently affecting
> account 'foo' or requsting "Grapple Grommets," the expectation should
> be that the balance will end up as the starting balance less the total
> value of the 25 transactions.
>
> All sorts of other parts of processing might take place in parallel,
> but those specific 25 updates must take place in some serialized
> order.

But it is up to the RDBMS, not my application, to do that. My application can remain blissfully ignorant, in the situation you give.

> You might be able to throw those balance updates into a queue, and
> have a separate process do the job, thus allowing that to be
> serialized separately.
>
> Thus, each transaction contains something like:
>
> insert into balance_updates (account_id, amount) values ('foo', -5);
>
> insert into inventory_updates (product_id, amount) values ('Grapple
> Grommets', -5);
>
> And a separate process grabs the _updates tables later and updates the
> balances.

Of course, this only works in the rather unusually circumstances you give. I rarely want to decrement a column unconditionally. For example, if there are only 5 "Grapple Grommets", and I get 25 request for one each, then 20 of those request need to be denied. I can't carry an inventory of negative 20 grommets. This does require code in my application (either cannot serialize or race condition checking, or if you insist, explicit locking), and that code must be there no matter what versioning scheme is used.

> That amounts to shifting the lock from one place to another. It may
> be worthwhile, but it doesn't involve locking _disappearing_.

The locking obviously doesn't disappear from the RDBMS. It also doesn't disappear from my application code, since it was never there in the first place. That's my point. He said you can get rid of the locking from your application code. Well, it's just not there to begin with. The relevant code that is in my application, which is code to handle transactions, not explicit locking, needs to be there regardless.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB