[TransWarp] PROPOSAL: drop requirement for 'joinTxn=True' on SQL exec

[Phillip J. Eby]

At 05:45 PM 3/7/03 +0200, alexander smishlajev wrote:

>as for me, i found joinTxn feature very interesting: in many cases, i 
>access data for read only, and i wonder if reading data without joining 
>transaction may give some considerable performance improvement?

Not for the process that's doing the reading, no.  The specific performance 
issue I ran into was caused by reads blocking other processes from 
*writing*.  If you're using an MVCC database or one with an optimistic 
locking scheme, you will never have this problem.  If you do run into this 
problem, most likely you can change your transaction isolation level either 
at the connection or statement level.


>so i vote for leaving the "joinTxn" binding in the SQLCursor, but change 
>default behaviour from 'joinTxn=False' to 'joinTxn=True'.  then PEAK users 
>willing to disable transactional mechanisms will have an option to do so, 
>but they must know what they're doing.

I think this is a YAGNI, because what I would personally do is always write 
"offending" read queries with "AT ISOLATION LEVEL 0" or whatever the 
appropriate SQL clause is.  This also has the advantage of being more 
explicit for when you show a DBA or other SQL maven your code.  :)

Anyway, there's actually no benefit to not having the *connection object* 
join the transaction.  The only performance benefit you can get is by 
avoiding the read locks, and the databases that have this problem generally 
provide you with the means to address it in SQL.  And if your database 
doesn't have a way to specify it in SQL but it can be done with connection 
parameters or by avoiding transactions, you can use a second connection 
object that's set up appropriately.

Also, the semantics of 'joinTxn=False' are screwy.  What happens if you 
specify it when the transaction has already been joined?  Should that cause 
an error?  Since you are explicitly requesting an untransacted read 
operation, I would think it should result in an error, so that you know you 
aren't getting the requested semantics.  Otherwise, we are right back in 
"silent failure" territory, just for a different audience.

So, for these reasons I do not wish to continue any usage of 'joinTxn'; the 
PEAK system was the wrong place for me to put a workaround for an issue 
that is fully addressable via standard SQL syntax.