Thread: SERIAL order and INSERT order
Hi I have a table defined as follows: CREATE TABLE mytable ( seqnumber BIGSERIAL; ... ) and I have multiple threads inserting into the table. If multiple threads insert at the same time, will the sequence numbers generated always appear in the table in order? e.g. thread 1 does INSERT getting serial number 1 thread 2 does INSERT getting serial number 2 and both complete successfully (no rollbacks). 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? thanks
On Tue, Jun 14, 2011 at 1:30 PM, <l1@nym.hush.com> wrote: > Hi > > I have a table defined as follows: > > CREATE TABLE mytable ( > seqnumber BIGSERIAL; > ... > ) > > and I have multiple threads inserting into the table. > > If multiple threads insert at the same time, will the sequence > numbers generated always appear in the table in order? It's going to depend on how you define 'order'. I take it you mean transaction commit order based on time. sequence numbers are always *fetched* in order. however, if two separate threads are grabbing numbers and inserting them roughly a the same time, there could be a race to transaction commit (although I wonder how that could possibly matter, since your app is multi threaded and there is no distinguishing information outside of the sequence value itself). merlin
l1@nym.hush.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 orderrows were inserted you should use a timestamp that is recording that time (although I believe the resolution of a timestampcolumn might be not fine enough..) Regards Thomas
Thomas Kellerer wrote: > l1@nym.hush.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
Thanks. The issue is we have some externally driven constraints on this particular column. Anyway, I can enforce ordering another way. On Wed, 15 Jun 2011 07:27:53 -0400 Bob McConnell <rmcconne@lightlink.com> wrote: >Thomas Kellerer wrote: >> l1@nym.hush.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 > >-- >Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-novice