Re: Best database for web backend on Linux?

In article <70ijo9$r3i$1_at_news.bctel.net>, "Thor" <thor_at_echidna.net> writes:
> It sure saves a tonne of coding (grab the sybase docs and read the =
>timestamp work they suggest for high availability and avoid blocking, =
>yuck).

I think that you're speaking of Sybase's "browse mode". That idiotic name is for a very well-understood and useful technique called "optimistic concurrency".

Remember that locking is not, by itself, the goal. It is a tool to achieve "serializability": the appearance that all accesses to the database are sequential, even though they're not. This also refers to the famous "levels of isolation", which define just how important this serializability is (lower isolation levels give up certain details of serialization in exchange - one hopes! - for performance gain).

Locking is a good tool for brief operations (transactions) where contention is expected to be reasonably frequent and reexecution of the operation is unacceptable. Optimistic concurrency, on the other hand, is useful where transactions are longer, the frequency of contention is lower, and operations may be repeated in the case of a "concurrency failure".

I think that the name "browse mode" comes from the idea that a user can be looking at a "data browser" for an infinite period of time before changing the data and saving the changes. During this period, the data may be updated by another person or process. Locking would have prevented that, but that assumes that you know ahead of time that the user will actually make a change. Optimistic concurrency is a perfect solution. If the user changes something that has been changed between now and when the user last retrieved the data, the data browser issues an error.

Emacs has the same feature, happily. So do several configuration management and source control tools. RCS, for example, supports both locking and optimistic concurrency. SUN's Teamware uses optimistic at a certain point and locking at another. Teamware also has a nice "merge" tool for concurrency failures.

You don't need support in the database for optimistic concurrency, but you do need the agreement of all processing writing to the effected structures. In that, optimistic concurrency is "advisory" while locking is "enforced".

With respect to row level locking vs. page level locking, I'd like to remind everyone that "it depends". You can think of it like a "sparse matrix" object vs. a "full matrix" object. Which is more efficient?

• Andrew

P.S.	Before I worried about "page locking" vs. "row locking", I'd
	check a few things.  My favorite example was a client that
	had a (clustered) index on a monotonically increasing serial.
	That's going to have contention problems amongst writers regardless
	of the locking scheme (don't forget that index tree nodes require
	locking too).