Re: Best database for web backend on Linux?

In article <70tnvk$g0k$1_at_news.bctel.net>, "Thor" <thor_at_echidna.net> writes:
>Andrew:
>That is interesting ... as I flip to the example in my Sybase book again
>(from the Sybase training course), it's exactly as I have stated it. Who
>maintains the serial in your description??
>

When you're using "browse mode", the server (mostly). But if you're doing this "by hand", then the serial can be any monotonically increasing (or decreasing, I suppose) value. Counter, timestamp, etc. would all work.

In the "by hand", case, the update would look something like:

	UPDATE ...
			serialColumn = @oldSerialValue + 1
			...
		WHERE
			...
			AND	serialColumn = @oldSerialValue

(if we're using a counter)

However, there is an issue that I am fudging (which may be why there's disagreement with what Sybase's manual claims). I'm assuming that the UPDATE is the only write operation in the transaction.

If that is not the case - if the transaction actually consists of multiple data-changing operations - then there is an additional problem to be considered:

                Thread 1 Thread 2

t1. 		Read R1
t2.		Read R2
t3.		Write R1'
t4.					Read R1'
t5.					Read R2
t6.					Write R1''
t8.					Write R2''
t9. 		Write R2'

The write at t9 fails with a concurrency failure. So the write of t3 must be rolled back. This has the side effect of forcing all of Thread 2's operations to be rolled back. This is called "Cascade Rollback".

I'm guessing that Sybase avoids this by holding a lock from t3 until Thread 1's commit/rollback. In defense of this, I'd state that (1) cascade rollback is a very good thing to avoid for several reasons, and (2) the lock is being held only for a very brief period, in contrast to a lock established at t1. Reason #2 is especially significant if you recall that, in the context of the current discussion, we don't know at t1 whether or not the write at t3 will ever occur.

Actually - if I remember correctly - *real* optimistic concurrency is a little different from what I've described. It tracks both "last read" and "last write" time, but I don't recall what it does with "last read" time. I believe that it was another aid in avoiding cascading rollback, but I'd have to check a text (my favorite being Ullman's for these issues) to be sure.

Is the material from which your example was taken online? If so, I'd appreciate a URL; I'd like to see it. As you can probably tell, most of what little I know of databases has come from more generic sources.

BTW, one thing that I've not seen in the discussion of row level locking vs. page level locking is the idea of promotion. It seems to me that many of the "row level locking is too expensive" issues will be mitigated by proper specification of the density of row locks which would be promoted to a page lock.

• Andrew