Re: [racket-users] db: nested transactions
Hi Ryan, On 6/23/2015 12:20 PM, Ryan Culpepper wrote: Yes, that should be fine. One note about your sample code: the isolation mode of inner transactions must be #f (the default); you can't change isolation levels once you've started an outer transaction. Also keep in mind that nested transactions are not supported for ODBC connections. Yes, I saw in reading some more that both call-with-transaction and start-transaction create a savepoint if an outer transaction is open. Savepoints are very nice but they don't handle every situation - sometimes you really need a full subtransaction. Using savepoints means, e.g., that if I need serialize isolation anywhere, I have to use serilize isolation everywhere. For many situations, that's hitting a fly with a sledgehammer and severely impacts performance. There are lots of cases where you need to serialize a particular operation inside a much larger transaction that mostly can run with lower isolation. [He asks naively not having looked at the code:] Would it be hard to keep savepoint creation if #:isolation is #f but allow other settings to create a real subtransaction?At least if #:isolation is 'serialized? I can't think of any realistic case where a subtransaction would require higher isolation but not need to be serialized. Thanks, George -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] db: nested transactions
On 06/24/2015 07:46 AM, George Neuner wrote: Hi Ryan, On 6/23/2015 12:20 PM, Ryan Culpepper wrote: Yes, that should be fine. One note about your sample code: the isolation mode of inner transactions must be #f (the default); you can't change isolation levels once you've started an outer transaction. Also keep in mind that nested transactions are not supported for ODBC connections. Yes, I saw in reading some more that both call-with-transaction and start-transaction create a savepoint if an outer transaction is open. Savepoints are very nice but they don't handle every situation - sometimes you really need a full subtransaction. Using savepoints means, e.g., that if I need serialize isolation anywhere, I have to use serilize isolation everywhere. For many situations, that's hitting a fly with a sledgehammer and severely impacts performance. There are lots of cases where you need to serialize a particular operation inside a much larger transaction that mostly can run with lower isolation. [He asks naively not having looked at the code:] Would it be hard to keep savepoint creation if #:isolation is #f but allow other settings to create a real subtransaction?At least if #:isolation is 'serialized? I can't think of any realistic case where a subtransaction would require higher isolation but not need to be serialized. I haven't been able to find much information about real subtransactions as opposed to savepoints. What DB systems support them? How are they created and managed? If it's through SQL commands, then it might not be too difficult. Ryan -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] db: nested transactions
[ Email says sending to the group failed. Apologies if this appears multiple times. ] Hi Ryan, On 6/24/2015 10:06 AM, Ryan Culpepper wrote: On 06/24/2015 07:46 AM, George Neuner wrote: [He asks naively not having looked at the code:] Would it be hard to keep savepoint creation if #:isolation is #f but allow other settings to create a real subtransaction?At least if #:isolation is 'serialized? I can't think of any realistic case where a subtransaction would require higher isolation but not need to be serialized. I haven't been able to find much information about real subtransactions as opposed to savepoints. What DB systems support them? How are they created and managed? If it's through SQL commands, then it might not be too difficult. Hmm ... hadn't thought about which DBMS support them. You're right that that information is hard to find. I know SQLServer and Sybase support them. (Quite) A bit of searching shows that not many others do. Nesting is required for 2-phase commit [so I would have thought all production quality DBMS would do it] but it seems that most DBMS now only *support* 2-phase commit by external controller and don't actually *do* 2-phase natively. Probably best to forget it. I can do what I need with locks - it's just that using transactions would have made the code simpler to understand. FWIW: subtransaction is just jargon, there's no real notion separate from a normal transaction. It's simply that DBMS which allow nesting can enforce strict stack scoping so that inner transactions must complete before outer. Commits on inners are provisionary - visible within the outer but not to concurrently executing transactions until the outer commits. Different from savepoints in that inner transactions can specify different isolation. Thanks, George -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] db: nested transactions
Yes, that should be fine. One note about your sample code: the isolation mode of inner transactions must be #f (the default); you can't change isolation levels once you've started an outer transaction. Also keep in mind that nested transactions are not supported for ODBC connections. Ryan On 06/22/2015 05:05 AM, George Neuner wrote: Hi all, I have what I hope is a quick question. WIth appropriate care to pair start and commit/rollback, is it safe to use call-with-transaction and start-transaction together? e.g., (call-with-transaction dbc (lambda () : (start-transaction dbc #:isolation 'serializable) : (if (needs-rollback? dbc) (rollback-transaction dbc) (commit-transaction dbc)) : ) #:isolation 'read-committed ) or the reverse: (start-transaction dbc #:isolation 'repeatable-read) : (call-with-transaction dbc (lambda () : ) #:isolation 'serializable) : (if (needs-rollback? dbc) (rollback-transaction dbc) (commit-transaction dbc)) I am adding functionality involving sub-transactions to an existing code base that uses call-with-transaction pretty much exclusively and I'm hoping not to have to change the existing framework. Thanks, George -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com mailto:racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[racket-users] db: nested transactions
Hi all, I have what I hope is a quick question. WIth appropriate care to pair start and commit/rollback, is it safe to use call-with-transaction and start-transaction together? e.g., (call-with-transaction dbc (lambda () : (start-transaction dbc #:isolation 'serializable) : (if (needs-rollback? dbc) (rollback-transaction dbc) (commit-transaction dbc)) : ) #:isolation 'read-committed ) or the reverse: (start-transaction dbc #:isolation 'repeatable-read) : (call-with-transaction dbc (lambda () : ) #:isolation 'serializable) : (if (needs-rollback? dbc) (rollback-transaction dbc) (commit-transaction dbc)) I am adding functionality involving sub-transactions to an existing code base that uses call-with-transaction pretty much exclusively and I'm hoping not to have to change the existing framework. Thanks, George -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [racket-users] db: nested transactions
On 22/06/2015 11:05, George Neuner wrote: Hi all, I have what I hope is a quick question. WIth appropriate care to pair start and commit/rollback, is it safe to use call-with-transaction and start-transaction together? e.g., (call-with-transaction dbc (lambda () : (start-transaction dbc #:isolation 'serializable) : (if (needs-rollback? dbc) (rollback-transaction dbc) (commit-transaction dbc)) : ) #:isolation 'read-committed ) or the reverse: (start-transaction dbc #:isolation 'repeatable-read) : (call-with-transaction dbc (lambda () : ) #:isolation 'serializable) : (if (needs-rollback? dbc) (rollback-transaction dbc) (commit-transaction dbc)) I am adding functionality involving sub-transactions to an existing code base that uses call-with-transaction pretty much exclusively and I'm hoping not to have to change the existing framework. Thanks, George -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com mailto:racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. The documentation says: Calling either commit-transaction or rollback-transaction when the open transaction was created by call-with-transaction causes an exception to be raised. But they can be nested by further call-with-transaction calls, it says. Last time I did an O/R mapper (in Java) I decided to put in a safety belt and use transaction tokens because non local jumps in GUI code are known to occur and involontary nesting may be hard to detect. My O/R mapper requested to use a valid transaction token from the GUI code each time an object wanted to start a transaction. Different transaction used different tokens and unmatched tokens produced an error. I think I saw three of these error conditions packed nicely into error messages - all in all three of them with testing included. There is the overhead of token passing but it's still worth it. BTW Requesting isolation level support info from MySQL DB made me shudder back then: or there was some hidden HyperOracle release beneath mysql or that thing just lied about it's capabilities. -- You received this message because you are subscribed to the Google Groups Racket Users group. To unsubscribe from this group and stop receiving emails from it, send an email to racket-users+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
