469,167 Members | 1,453 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Transactions and insertion ordering

Hi,

I'm trying to work out how to make sure things are read from a table in
a consistent order. The table represents a queue of items and also the
history of those items.

Even with "serializable" transaction isolation I can begin two
transactions, insert a record in each, commit the second transaction
first. This second record is now visible and can be read from the queue.
But when I commit the first this one appears before the second one. This
could then be read from the queue second but when I rescan the table to
view history it looks like it was read first.

Are there any ways to make this work a little more intuitively?
Basically I guess I want to be able to model a queue effectively.

James
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
6 1382
nextval() should return value in the order they were called, rather
than commit time. I hope you're not relying on the unordered results of
a table scan remaining stable. Tables have no intrinsic "order", only
one inposed by an external sequence.

Hope this helps,

On Thu, Jun 10, 2004 at 09:28:50AM +0100, James Pharaoh wrote:
Hi,

I'm trying to work out how to make sure things are read from a table in
a consistent order. The table represents a queue of items and also the
history of those items.

Even with "serializable" transaction isolation I can begin two
transactions, insert a record in each, commit the second transaction
first. This second record is now visible and can be read from the queue.
But when I commit the first this one appears before the second one. This
could then be read from the queue second but when I rescan the table to
view history it looks like it was read first.

Are there any ways to make this work a little more intuitively?
Basically I guess I want to be able to model a queue effectively.

James


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAyDTVY5Twig3Ge+YRAjwZAJ0RhFkKEDuooL0p1Qt26W x24/5wDQCcDEwj
BEY7PEFrpP7qlBtCRzhsf2U=
=/RJx
-----END PGP SIGNATURE-----

Nov 23 '05 #2
nextval() should return value in the order they were called, rather
than commit time. I hope you're not relying on the unordered results of
a table scan remaining stable. Tables have no intrinsic "order", only
one inposed by an external sequence.

Hope this helps,

On Thu, Jun 10, 2004 at 09:28:50AM +0100, James Pharaoh wrote:
Hi,

I'm trying to work out how to make sure things are read from a table in
a consistent order. The table represents a queue of items and also the
history of those items.

Even with "serializable" transaction isolation I can begin two
transactions, insert a record in each, commit the second transaction
first. This second record is now visible and can be read from the queue.
But when I commit the first this one appears before the second one. This
could then be read from the queue second but when I rescan the table to
view history it looks like it was read first.

Are there any ways to make this work a little more intuitively?
Basically I guess I want to be able to model a queue effectively.

James


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAyDTVY5Twig3Ge+YRAjwZAJ0RhFkKEDuooL0p1Qt26W x24/5wDQCcDEwj
BEY7PEFrpP7qlBtCRzhsf2U=
=/RJx
-----END PGP SIGNATURE-----

Nov 23 '05 #3
On Thu, Jun 10, 2004 at 20:15:53 +1000,
Martijn van Oosterhout <kl*****@svana.org> wrote:
nextval() should return value in the order they were called, rather
than commit time. I hope you're not relying on the unordered results of
a table scan remaining stable. Tables have no intrinsic "order", only
one inposed by an external sequence.


This isn't really guarenteed with nextval. I think it will work if you
are just reserving one value at a time (which is the default).

I think the real problem is that the original poster needs to precisely
define what determines order. If the precise definition is transaction
commit order, I think that is going to be hard to do exactly right.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4
On Thu, Jun 10, 2004 at 20:15:53 +1000,
Martijn van Oosterhout <kl*****@svana.org> wrote:
nextval() should return value in the order they were called, rather
than commit time. I hope you're not relying on the unordered results of
a table scan remaining stable. Tables have no intrinsic "order", only
one inposed by an external sequence.


This isn't really guarenteed with nextval. I think it will work if you
are just reserving one value at a time (which is the default).

I think the real problem is that the original poster needs to precisely
define what determines order. If the precise definition is transaction
commit order, I think that is going to be hard to do exactly right.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5
On Thu, 2004-06-10 at 14:47, Bruno Wolff III wrote:
I think the real problem is that the original poster needs to precisely
define what determines order. If the precise definition is transaction
commit order, I think that is going to be hard to do exactly right.


Yes, that is what I want. So I can guarantee that the order of the IDs
in the database will be the same as the order in which they are taken
out of the queue.

I think I've come up with a reasonable solution now though. I can lock
the record representing the queue in another table FOR UPDATE and then
do the insert, then no other process will be able to gain that lock
until I complete. Best bit is other items can still insert concurrently,
but only one per queue, which is exactly what I was after.

Thanks for the help anyway ;-)

James
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6
On Thu, 2004-06-10 at 14:47, Bruno Wolff III wrote:
I think the real problem is that the original poster needs to precisely
define what determines order. If the precise definition is transaction
commit order, I think that is going to be hard to do exactly right.


Yes, that is what I want. So I can guarantee that the order of the IDs
in the database will be the same as the order in which they are taken
out of the queue.

I think I've come up with a reasonable solution now though. I can lock
the record representing the queue in another table FOR UPDATE and then
do the insert, then no other process will be able to gain that lock
until I complete. Best bit is other items can still insert concurrently,
but only one per queue, which is exactly what I was after.

Thanks for the help anyway ;-)

James
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Murad Nayal | last post: by
20 posts views Thread by Patrick Guio | last post: by
5 posts views Thread by John N. | last post: by
11 posts views Thread by Mike P | last post: by
reply views Thread by James Pharaoh | last post: by
10 posts views Thread by BLUE | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.