Re: Locking tables

| I want to put as much of the code as possible in the database front
| end for control and maintenance purposes. I have explicitly considered
| the performance degradation and am willing to accept it.
|
| Although having written this, maybe I should just lock everything in
| "access exclusive mode" for the brief periods of time these
| transactions will be pending, and not worry about identifying the
| _least_ restrictive that will block intercurrent Selects (until I
| actually do create a performance problem).

Beyond sequences and the other methods mentioned, there are a few other
ways to skin this cat without requiring a LOCK.

If you don't care that IDs are in sequence, you could make a field
UNIQUE and then randomly generate IDs and attempt to insert until you
succeed.

If you do need it in sequence, you can still use this brute force method
by getting the last value, incrementing and again continually attempt to
insert until you succeed.

Myself, I'm moving towards method #1 -- using random character IDs to
identify my records -- as makes it much easier to implement multi-master
replication. The key here is that by using character IDs, I can prepend
server location codes so that duplicate IDs are never generated across
servers.