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

Quoth Guido Stepken <stepken_at_little-idiot.de>:
> Yes, Billy, there is a solution for avoiding locks of any sort. A SQL
> Server can be programmed lock free, even if you do not unterstand the
> very clever idea of MVTO (multi versioning timestamp odering), as i
> pointed out in several postings. In MVTO all datasets have timestamp
> as well as the transaction manager itself. With MVCC and MVTO it is
> possible to delay one transaction as long, as the other transaction
> writing into the database. Then, if done, the delayed transaction
> recapitulates, what should have been done and updates the data in this
> way, as if a lock had been set for avoiding collisions.
> Its a very clever trick to time - shift two simulaneous writing events
> on the same data set, in order to avoid any collision.
>
> I am sorry for you, that even you still think, locks are
> necessary. They aren't, neither from practical, nor from theoretical
> point of view.

So how would you cope, without locks, with the following set of transactions that are submitted concurrently:

begin transaction;
update inventory set quantity = quantity - 5 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity - 1 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity - 2 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity + 8 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity - 3 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity - 5 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity + 7 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity + 1 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity - 5 where id = 175; end transaction;

begin transaction;
update inventory set quantity = quantity + 5 where id = 175; end transaction;

By the way, the schema was:

create table inventory (

   id integer,
   quantity integer,
   constraint conservation_of_mass

       check quantity >= 0
);
insert into inventory (id, quantity) values (175, 25);

Those updates are all totally legitimate, and should work out fine. But they _cannot_ be applied concurrently because they all affect the same row (with id = 175).

Surprise, surprise, the row can and will be locked so that those concurrent transactions will all execute serially.

-- 
let name="aa454" and tld="freenet.carleton.ca" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/postgresql.html
Eagles may soar, free and proud, but weasels never get sucked into jet
engines.