Issue with transaction

[jcompagner]

hans explained it exactly right.

A transaction only really does something at the first moment a connection is used for that client
Before that it is only state inside the client.
(startTransaction doesnt result in a call to the server at all)

[ROCLASI]

(startTransaction doesnt result in a call to the server at all)

Eh, what ? So you don't use database transactions at all ???

EDIT: oh wait...I should read the thread better.
So the actual call it send to the backend db when it does a save.

So lets say I want to do a bunch of SELECT's in a db transaction to make sure I get a consistant snapshot of some data (race conditions, ya know) then this won't work since I don't save any data. Am I correct in thinking that ?

[jcompagner]

that will also work, at the moment you make a server call the transaction id (the state in the client) is send to the server and a connection is reserved for that transaction.

But how do you do your selects? Select for update? else you still are not sure that it is consistent..

[ROCLASI]

But how do you do your selects? Select for update? else you still are not sure that it is consistent..

No, just selects. My writes are done in db transactions. When I do, lets say, 3 selects to build up a report or export and after select 1 another client saves data then my select 2 and 3 might fetch (related) data that select 1 didn't have. Hence the use of a db transaction for a select.

Although now you make me wonder that this might not work the way I expect it to work...

Edit: I see I need to set the isolation level of the transaction to 'SERIALIZABLE' to be able to do that (on Pg).

[Hans Nieuwenhuis]

Hi,

I am not sure about other databases, but I know how Oracle handles this.
(and I am learing about postgresql)

Transaction-Level Read Consistency
Oracle also offers the option of enforcing transaction-level read consistency. When a transaction runs in serializable mode, all data accesses reflect the state of the database as of the time the transaction began. This means that the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries made by a serializable transaction do see changes made by the transaction itself. Transaction-level read consistency produces repeatable reads and does not expose a query to phantoms.



Read committed This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.
Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.


Serializable Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.
Read-only Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.



Set the Isolation Level
Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION READ ONLY;

To save the networking and processing cost of beginning each transaction with a SET TRANSACTION statement, you can use the ALTER SESSION statement to set the transaction isolation level for all subsequent transactions:

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;

ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

[ROCLASI]

Hi Hans,

yes, I just found it in the Pg manual.
http://www.postgresql.org/docs/9.0/stat ... ction.html
'READ COMMITTED' is the default on PostgreSQL.

[jcompagner]

But how do you do your selects? Select for update? else you still are not sure that it is consistent..

No, just selects. My writes are done in db transactions. When I do, lets say, 3 selects to build up a report or export and after select 1 another client saves data then my select 2 and 3 might fetch (related) data that select 1 didn't have. Hence the use of a db transaction for a select.

Although now you make me wonder that this might not work the way I expect it to work...

Edit: I see I need to set the isolation level of the transaction to 'SERIALIZABLE' to be able to do that (on Pg).

or i guess use something like select for update. Then it should lock the things you update and you can calculate on that data the thing you want to update and do the commit
others that also want to do that also try to select for update but those will be blocked.

Servoy has support for locking rows and using select for update in the database for that..

[ROCLASI]

Hi Johan,

I prefer to stay away from any locking if I can.
Anyway, just setting the isolation level to 'SERIALIZABLE' does the trick.

[Hans Nieuwenhuis]

But I do not know if it is possible in Servoy to set the
isolation level for a transaction ?

Maybe a nice new feature ??

[ROCLASI]

Hi Hans,

If I am not mistaken when you start a transaction then your rawSQL plugin will also use this transaction. So you can send the SQL after you started the transaction using the rawSQL and then everything you do in that transaction (using Servoy objects or otherwise) will use use this transaction (and isolation level).

[jcompagner]

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)

[ROCLASI]

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.

[Hans Nieuwenhuis]

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

I don't how serious this influence is.

[ROCLASI]

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...