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

From: Guido Stepken <stepken_at_little-idiot.de>
Date: Mon, 18 Aug 2003 20:29:48 +0200
Message-ID: <bhr61h$or7$02$1@news.t-online.com>

Aaron Bertrand - MVP wrote:

>>possible to delay one transaction as long, as the other transaction
>>writing into the database.

>
>
> What is the big evil of a lock? Making a user wait for the lock to be
> released. So, what is the difference between a lock, and the sequence of
> events you describe above? In both cases, there is a delay...
>
>

Locks are evil, because in SQL Server you have to decide either selects from clients are being blocked in three transaction modes (COMMITTED, SERIALIZE, REPEATABLE READ), or you have to use UNCOMMITTED mode and you risk dirty reads, phantom entries from other transactions. SQL Server suffery very long blockades due to locks, which block other transactions. See the tests in pcmag, SQL Server shows chaotic behaviour and many errors, which can be avoided.

Without locks, working with time shifted transactions on isolated datasets (thanks to MVCC in PostgreSQL), a SQL Server can raise to its maximum power, without errors and hangers..

I know, my point of view is unpopular, but very well researched. Reed has invented this method of rearranging transactions (time - shifting) and updates (lost update problem) in that way, that no locking at all is necessary. I is like magic, programmers never ever have to care about concurrent write events any longer, because they are time - shifted and rearranged in a manner, as if a lock had been set. You can do this by putting all timestamps on raws and transactions into context and setting the data sets to that value, as if a lock had been set. You simulate the result of locks on datasets afterwards, as if locks had occurred.

The performance in average applications increases about 30%, and increases several 100% in applications, which do heavy updates, statistics, reads, insertions simultaneously.

With a simple benchmark test i can show, that SQL Server breaks down at minimum load. Oracle is much better at read-write (not at write-write), PostgreSQL with abolute independant MVCC (write-write, write-read ist possible, without any locking...) is hitting the top. No locks have to be set in PostgreSQL, but - MVCC is the minimum requirement to implement MVTO. Setting timestamps on transactions and datasets, so a server side logic can time - shift transactions and rearrange data content, as if a lock had been set. This is a simple script in pl/SQL.

regards, Guido Stepken Received on Mon Aug 18 2003 - 13:29:48 CDT