3 ms·
> "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level. Explain further. I'm not a Postgres 'expert
by davidw 17y ago
> "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level.
Explain further. I'm not a Postgres 'expert', just a happy user, and I'd love to actually learn something from a silly X vs Y thread.
- jrockway 17y agoPostgres defaults to "read committed" isolation, rather than repeatable read or serializable isolation. I have mostly noticed the difference when dealing with updating HTTP session objects in a transaction; with "read commited" isolation, concurrent requests can overwrite newly-written data. With serializable isolation, the transaction that tries to overwrite new data with older data (read at the beginning of the request) is aborted. (The fix for this is difficult, but I basically don't use the session for much transient data anymore, so this became less of an issue. Before setting up the database to be strict, I never even thought of this as a problem. But obviously it is.)
- neilc 17y agoI wouldn't say it is "obviously" a problem: READ COMMITTED are perfectly reasonable semantics, and are preferable to many applications (e.g. many apps aren't written to retry transactions that abort due to serialization conflicts). Which isolation level ought to be the default is a matter of debate, but I don't think using RC by default is "obviously" wrong -- if an application depends on a particular isolation level for correctness, it should set it explicitly in any case.
- jrockway 17y agoJust remember that with read committed isolation, certain access patterns can silently trash your data. People are afraid of transactions aborting for some reason, as they think that's an error. It's not; you just retry your transaction, which will now be using correct data. "Read committed" opens you up to race conditions in exchange for transactions rarely (never?) aborting themselves. I think the default should be "never trash my data without telling me".
- gchpaco 17y agoPostgres defaults to READ COMMITTED transaction isolation, where it is possible that if you perform the same SELECT twice you will get different answers due to a transaction committing in between. This can affect updates in some circumstances, although if you build your database so that everything is done with INSERTs then it's pretty harmless. Postgres permits you to specify the SERIALIZABLE transaction level which is complete isolation, if you care about this. MySQL does not.
- ams6110 17y agoThe important thing is to understand your database's concurrency model. They don't all work the same way. This causes problems e.g. for SQL Server developers who are new to Oracle, or vice versa, and naively assume that everything is the same.