Re: SERIAL order and INSERT order

Thomas Kellerer wrote:
> l1(at)nym(dot)hush(dot)com, 14.06.2011 20:30:
>
>> Are there any cases where an observer might see a row with serial
>> number 2 but not yet see serial number 1, perhaps because thread 1
>> hasn't completed its insert yet? For example SELECT * WHERE
>> seqnumber< 3; would return only a row with seqnumber=2?
>
> It is never a good idea to rely on the numeric ordering of a generated
> PK column. If you really need to know in which order rows were inserted
> you should use a timestamp that is recording that time (although I
> believe the resolution of a timestamp column might be not fine enough..)

Any time you have multiple threads, processes or processors accessing
the same database, race conditions like this are not only possible but
very likely. It is the nature of the multi-processing beast. You cannot
guarantee that every insert will be completed before the next one, since
you cannot guarantee that every scheduler involved will recognize that
the inserts should be atomic. In fact, most of them won't. In the case
of multiple computers, you cannot even guarantee that the CPU in the
first one in will be as fast as the next one. There are simply too many
variables that you cannot control.

The seqnumbers will be assigned at some point in each transaction, in
the order that they reach that point. But the transactions are not
complete until they are committed, and that cannot be guaranteed to be
in the same order as the assignments. The results of any query that is
processed between those two events will reflect the state of the
database at that point in time. So yes, there may be a missing seqnumber
or two on occasion. Any expectations to the contrary are unrealistic.

Bob McConnell
N2SPP