Issue with transaction

Classic Servoy
41

Serializable is horrible for performance, and i guess to make it work, all connections must be in that state so everything that you do then is the serialized after each other…

But robert: your problem is not fixed by using Serializable . The only thing if you use select values to build up something that you update, for example a warehouse inventory stock variable.
You have to use locks… It could be that Serializable does that for you… and yes some databases seems to do that, they lock everything you select!!!
But we are mostly talking about posgresql here and postgresql (and oracle) has the nice feature of snapshot isolation. (http://en.wikipedia.org/wiki/Isolation_ … systems%29)

read this also for example:

http://www.postgresql.org/docs/8.4/stat … n-iso.html

"When a transaction is using the serializable level, a SELECT query only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. (However, the query does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) "

and thats horrible! So your data that you select you never see the any updates anymore, your state is consistent but others could have changed it!
(Read committed does give you the data again)

I guess if you use all kind of select data to update 1 row you still need to use select for update:

“UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the serializable transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the serializable transaction can proceed with updating the originally found row.”

And if you have to use that then you can just use READ_COMMITTED and select for update (as far as i understand all the doc in that link)

42

Hi Johan,

I don’t think you understand my requirement.
I don’t want to see any commits from others in my transaction. Also I won’t be doing any updates either in this transaction.
All I want is that my SELECTs see consistant data from the moment I start my first SELECT.
That’s what the serialization isolation level gives me.
And it’s not horrible for performance, there are no locks involved either. Why would it perform badly ? This is MVCC we are talking about.

43

Well, it will influence database performance as it has
to do more housekeeping during the transaction.

I don’t how serious this influence is.

44

True, there is always some more overhead. But in this case a very manageable overhead.
This transaction will be open for 1-2 seconds only to do an export. The consistant state is important because some config files for some critical services are generated with it.
This action will only be done periodically and by 1 user only (most likely a batch processor).
I don’t think the overhead will even register…