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

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

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.

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.

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

-- 
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/unix.html
Rules of the  Evil Overlord #135.  "My doomsday  machine will have the
advanced technological device called  a capacitor just in case someone
inconveniently pulls the plug at the last moment. (If I have access to
REALLY  advanced technology, I  will include  the even  better back-up
device known as the "battery.")"  <http://www.eviloverlord.com/>