I'm thinking about implementing a queue in an Oracle database basically using a table as the queue and am trying to decide how to prevent multiple sessions from inserting into the table at the same time and messing up the ordering.
Is doing a
LOCK TABLE mytable IN EXCLUSIVE MODE the only way to prevent multiple sessions from inserting into a table?
"Queue" is probably not the right term here. More of a list, in which the relative order between the elements is what's important. I'm thinking of something on the order of Java's ArrayList.
Are you sure you need to implement your own queue? Oracle provides built-in queuing (called Oracle Advanced Queuing). That would certainly tend to be a better option than implementing the same thing yourself.
If you do implement a queue yourself, why would you need to prevent concurrent inserts? What order are you concerned with being "messed up"? Assuming you are using an Oracle sequence object to populate the primary key, I don't see any reason that you'd have to prevent concurrent updates.
It's completely unclear to me what problem you're trying to avoid.
Explicit locking provides serialization, but doesn't seem to provide any meaningful, deterministic order of insertion. If two sessions tried to insert "at the same time", which one gets the lock first is essentially random. You'd get the same effect without explicit locking if the table has a primary key, since multiple inserts would have to serialize on that to enforce uniqueness. But even without that, one session would end up inserting (or more to the point, committing its insert) before the other.
It sounds like you have some sense of an order the items need to be in, independently of the order in which they are inserted. Without more detail, my reaction to that is that the table should have a column that indicates the ordering, and readers of the table should make use of that to properly sort the records. Recall that database tables have no inherent ordering; just because one row is inserted before another does not mean that they are stored in that order or returned to queries in that order.