Обсуждение: Performance question: Commit or rollback?
Some of my code involves transactions which will not have significant effect on the database. It might be a read-only transaction (possibly declared as one, but possibly not), or perhaps a completely empty transaction - I have a framework that will always open a transaction, then call on other code, and then complete the transaction at the bottom. In these instances, is it better to commit or to rollback? Are there performance penalties to either option? Similarly, what about when the first action in a transaction puts it in an error state? Is it better to commit (which should have no effect - nothing succeeded) or to roll back? I could test these things experimentally, but am afraid I'll skew my results based on the data I use. Hoping that somebody here knows for certain - there does seem to be a wealth of expertise here. Chris Angelico
On Sat, Dec 17, 2011 at 6:53 PM, Chris Angelico <rosuav@gmail.com> wrote: > Some of my code involves transactions which will not have significant > effect on the database. It might be a read-only transaction (possibly > declared as one, but possibly not), or perhaps a completely empty > transaction - I have a framework that will always open a transaction, > then call on other code, and then complete the transaction at the > bottom. > > In these instances, is it better to commit or to rollback? Are there > performance penalties to either option? I do not believe there are performance penalties for either. All commit or rollback does is determine visibility of changes made. Best Wishes, Chris Travers
On Sun, Dec 18, 2011 at 2:02 PM, Chris Travers <chris.travers@gmail.com> wrote: > I do not believe there are performance penalties for either. All > commit or rollback does is determine visibility of changes made. Thanks. (And thanks for the incredibly quick response!) My framework has a "read-only mode" (determined by user-level access), in which it begins a read-only transaction. At the end of it, I currently have it rolling the transaction back (to make absolutely sure that no changes will be made), but was concerned that this might place unnecessary load on the system. I'll stick with rolling back, since it's not going to hurt! Chris Angelico
On Sun, 2011-12-18 at 14:05 +1100, Chris Angelico wrote: > On Sun, Dec 18, 2011 at 2:02 PM, Chris Travers <chris.travers@gmail.com> wrote: > > I do not believe there are performance penalties for either. All > > commit or rollback does is determine visibility of changes made. > > Thanks. (And thanks for the incredibly quick response!) > > My framework has a "read-only mode" (determined by user-level access), > in which it begins a read-only transaction. At the end of it, I > currently have it rolling the transaction back (to make absolutely > sure that no changes will be made), but was concerned that this might > place unnecessary load on the system. I'll stick with rolling back, > since it's not going to hurt! > > Chris Angelico > The actual rollback won't hurt as long as you have not made any modificatons to any records. But opening the transaction could have side effects for other processes that want to modiy the records that you want to protect in your read-only transaction. How about using a databaseuser that has it's create/update/delete rights revoked? That will cause an error if the supposedly read-only routine does try to change data.
On Sat, Dec 24, 2011 at 11:46 PM, vinny <vinny@xs4all.nl> wrote: > The actual rollback won't hurt as long as you have not made any > modificatons to any records. But opening the transaction could have side > effects for other processes that want to modiy the records that you want > to protect in your read-only transaction. > > How about using a databaseuser that has it's create/update/delete rights > revoked? That will cause an error if the supposedly read-only routine > does try to change data. The readonly-ness of the session is defined based on information stored in the database, so that would entail the cost of re-authenticating. Also, we want to minimize debugging time by having both read-only and read-write access use almost exactly the same code and DB access, meaning that we should not need to test every module in every mode. ChrisA
On Sat, 2011-12-24 at 23:49 +1100, Chris Angelico wrote: > On Sat, Dec 24, 2011 at 11:46 PM, vinny <vinny@xs4all.nl> wrote: > > The actual rollback won't hurt as long as you have not made any > > modificatons to any records. But opening the transaction could have side > > effects for other processes that want to modiy the records that you want > > to protect in your read-only transaction. > > > > How about using a databaseuser that has it's create/update/delete rights > > revoked? That will cause an error if the supposedly read-only routine > > does try to change data. > > The readonly-ness of the session is defined based on information > stored in the database, so that would entail the cost of > re-authenticating. Yes you would have to re-authenticate, you'd have to weigh the time-cost of that that against any performance hits the transaction might cause. > Also, we want to minimize debugging time by having > both read-only and read-write access use almost exactly the same code > and DB access, meaning that we should not need to test every module in > every mode. So, your read-only mode is basically a flag that forces your code to always issue a rollback at the end, instead of a commit for read/write mode. I find that a bit scary. :-) regard, Vincent.
On Sun, Dec 25, 2011 at 12:00 AM, vinny <vinny@xs4all.nl> wrote: > So, your read-only mode is basically a flag that forces your code to > always issue a rollback at the end, instead of a commit for read/write > mode. > > I find that a bit scary. :-) It's three things: 1) BEGIN TRANSACTION READ ONLY instead of BEGIN TRANSACTION 2) A high level flag that tells the PHP code that it ought not to change things 3) ROLLBACK instead of COMMIT ChrisA
Chris Angelico wrote: > On Sat, Dec 24, 2011 at 11:46 PM, vinny <vinny@xs4all.nl> wrote: >> How about using a databaseuser that has it's create/update/delete rights >> revoked? That will cause an error if the supposedly read-only routine >> does try to change data. > > Also, we want to minimize debugging time by having > both read-only and read-write access use almost exactly the same code > and DB access, meaning that we should not need to test every module in > every mode. You can do it correctly while reusing all of your code; you just have different arguments at connect time and otherwise your code uses the connection handle in the same way afterwards. Its fine to have flags in the app so the app just tries acceptable things, but privileges in the database are the only way to actually be safe and resilient against accidental changes. -- Darren Duncan
On Sat, Dec 24, 2011 at 11:34 AM, Darren Duncan <darren@darrenduncan.net> wrote: > > > You can do it correctly while reusing all of your code; you just have > different arguments at connect time and otherwise your code uses the > connection handle in the same way afterwards. Its fine to have flags in the > app so the app just tries acceptable things, but privileges in the database > are the only way to actually be safe and resilient against accidental > changes. -- Darren Duncan Agreed. Roles in the db are important (we use them extensively in LedgerSMB). However here is a case where it doesn't work so well: Suppose I want to run read-write test cases against a production database to look for things that are wrong but we want to be mathematically sure that the test cases do not commit data to the database. So I don't entirely know the best way to do this in other languages, but here is what we did in Perl: 1) Our application normally uses DBI (and DBD::Pg). 2) We have a special module (LedgerSMB::DBTest) which basically exposes the portions of the DBI/DBD::Pg interface we are using, but lies to the higher levels about committing. Basically it returns true, but does nothing database-wise, providing the appearance of a consistent set of changes but in fact the changes are still to be rolled back. 3) In our test case scripts, we switch out the DBI database handles with instances of LedgerSMB::DBTest. This works well because it is fails safely. If we omit something from the DBTest module, our code will error. If we have test cases that depend on some transactions committing and others rolling back, we have to order the test cases appropriately or the test cases fail. In other words, the worst we can get are test case failures, not spurious commits, and the changes necessary to make this happen are in the test case files themselves. This makes it easy to verify. Best Wishes, Chris Travers