Hello,
I am writing a database application that makes many attachments and updates with a fake isolation level serialisable( snapshot isolation).
In order not to make tons of network hits, I insert inserts and updates in one transaction with PreparedStatements. They should fail very rarely, because the inserts are pre-programmed and almost do not conflict with other transactions, so rollbacks often do not occur.
Having large transactions should be good for WAL, because it can drop large chunks and does not need to hide for mini-transactions.
1.) I can only see the positive effects of a large transaction. But I often read that they are bad. Why can they be bad in my use case?
2.) Is conflict checking so costly when local snapshotscombined back into a real database? The database will have to compare all record sets of possible conflicts (concurrent transaction). Or does he make some quick shortcuts? Or is it so cheap?
[ EDIT ] It might be interesting if someone can clarify how the snapshot isolation database checks if transactions that have overlapping parts on the timeline are checked for disjoint recordsets. Because this is what a fake serializable isolation level is all about.
source
share