Re: Best database for web backend on Linux?

In article <70jkv7$t5q$1_at_news.bctel.net>, "Thor" <thor_at_echidna.net> writes:
>Why are you assuming that row level locking requires you to lock the data on
>read/browse??? LONG transactions are even better when you can get atomic,
>row-level, locks rather than page locks. Unless you mean true long
>transactions, then neither scheme is worthy of that.
>
>If you look at most Oracle aware tools, they only lock that single record,
>iff that record has been modified.
>

If the updates are independent of the prior state of the data, what you describe will work. However, if the updates are dependent upon the state of the data prior to the update (which is the more common case in my experience), then the lock must take place at READ time.

That's why READ locks exist (and the other case is why lower levels of "isolation" (or serializability) exist).

For example, if your intention is to increment a value by one, you must

	t1: READ value
	t3: WRITE new value
	t4: cleanup (including releasing the lock)

If a lock is established only at t3, you've a loss of serializability. Consider a second process:

	t2: READ value
	t5: WRITE new value
	t6: cleanup

If we avoid locking, and use instead optimistic concurrency, the write at t5 will fail with a "serializability failure" event. The application would then decide upon the correct response (usually, rereading the data and reperforming the operation).

Of course, this scenario is more annoying when the "increment" is in fact a user operation being performed via some application. In that case, the write might or might not occur, and the time between the read and the write might include coffee breaks, vacations in Bali, or even just a slow typist. Locks held for that duration are often undesirable, even if at a fine level of granularity.

That's when optimistic concurrency provides a good solution.

• Andrew

P.S.	Our lives would be far easier if we didn't have to worry
	about those pesky users.