Обсуждение: OT: Question about transactions

I know this isn't strictly a JDBC issue, so feel free to ignore me.

If I've got one Java program that's doing database updates and then
firing events (through RMI/Observer/JMS, etc) to tell the other JVMs
to do some time-consuming things that fetch those items from the
database, is there a way for the other JVMs to get a consistent view
of the database?  For instance, if program 1 updates something in the
database and commits, and then tells everybody that's it's updated,
and then deletes the item from the database and commits, and then
tells everybody that it's deleted, is there a way that the other
programs can see the database as it was before the second commit until
they go to process the second message?  Could they do a "BEGIN
TRANSACTION" at the start and "END TRANSACTION" when they're done
processing, even though the processing only does queries and not
updates?  Or do I have to just suck it up and make it so that
everybody querying the database can handle things going away at
in-opportune times?

--
House: "Because it doesn't hurt here. Because I don't want to be in
pain, I don't want to be miserable."
Amber: "Well...You can't always get what you want."

On Tue, Aug 12, 2008 at 1:09 PM, Paul Tomblin <ptomblin@gmail.com> wrote:

> is there a way for the other JVMs to get a consistent view
> of the database?

My understanding is that PostgreSQL only supports two forms of
transaction Isolation: READ COMMITED AND SERIALIZABLE.  This means
that only the client that has opened the transaction can see
un-commited data.  All other client instances will see only the
commited data.

>  For instance, if program 1 updates something in the
> database and commits, and then tells everybody that's it's updated,
> and then deletes the item from the database and commits, and then
> tells everybody that it's deleted,

I'm not to sure on this one.  If the other JVM are using a seperate
connection, I wouldn't expect you to be able to achieve consistant
results. However, maybe postgreSQL's LISTEN and NOTIFY will do what
you need?
http://www.postgresql.org/docs/8.3/interactive/sql-listen.html
http://www.postgresql.org/docs/8.3/interactive/sql-notify.html


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Tue, Aug 12, 2008 at 04:09:48PM -0400, Paul Tomblin wrote:
> they go to process the second message?  Could they do a "BEGIN
> TRANSACTION" at the start and "END TRANSACTION" when they're done
> processing, even though the processing only does queries and not
> updates?

Try SERIALIZABLE mode.  If I understand you correctly, it will do what
you need.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Andrew Sullivan wrote:
> On Tue, Aug 12, 2008 at 04:09:48PM -0400, Paul Tomblin wrote:
>> they go to process the second message?  Could they do a "BEGIN
>> TRANSACTION" at the start and "END TRANSACTION" when they're done
>> processing, even though the processing only does queries and not
>> updates?
>
> Try SERIALIZABLE mode.  If I understand you correctly, it will do what
> you need.

That seems like it might work.  Thanks.

--
Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/
Conspiracies abound:  If everyone's against you, the reason can't
_possibly_ be that you're a fuckhead.
               -- The Usenet Guide to Power Posting