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

[Phillip J. Eby]

At 09:34 AM 3/5/03 -0500, Phillip J. Eby wrote:

>When you execute SQL that needs to be in a transaction, include 
>'joinTxn=True' in the keyword arguments to the call, e.g.:
>
>someConn('UPDATE something WHERE theother=whatever', joinTxn=True)
>
>
>>2. make changes that ensure a connection joins the Transaction
>>whenever it is used and supports transactions.
>
>For some apps and some connections, this is a bad idea.  See:
>
>http://www.eby-sarna.com/pipermail/transwarp/2003-January/000277.html

It's clear that this is becoming a FAQ, and more to the point, a bad 
one.  A good FAQ, in my opinion, is one that leads the asker to some 
understanding that goes beyond the scope of the specific application.  That 
isn't the case, here.


>Note, by the way, that it's safe for the SQL connection to not actually 
>join the transaction until the DM is flushing because of 
>'storage.commit()'.  Just be sure that any SQL you want to be in a 
>transaction either includes 'joinTxn=True', or is known to be issued after 
>a 'joinTxn=True' call has been made in that PEAK transaction.

The above is the only understanding that this FAQ potentially leads 
to.  However, if one is affected by this problem, then one doesn't need to 
find it out by tracking down what appears to be a bizarre and erroneous 
behavior by PEAK.  If you have the above problem, you'll know.  This 
appears to be a case where I erred on the side of supporting requirements 
that I had on a previous project, rather than doing the right thing in the 
general or the common case.

So, I declare 'joinTxn=True' to be un-PEAKish, because:

1) it requires you to be explicit about the common case, while being 
implicit for a peculiar special case, that can actually be fixed using pure 
SQL (using the "AT ISOLATION LEVEL" clause or a similar one in the SQL itself),

2) it has a "silent failure" mode, in that if you don't pay close attention 
you may not notice anything is wrong,

Amusingly, the users who found this, found it because they were using a 
database that doesn't autocommit SQL commands, so *by definition* they 
CAN'T be affected by the problem the 'joinTxn' flag is there to solve!

So, to resolve this issue, I propose to make the creation of a cursor 
always cause an SQL connection to join its transaction (thus invoking a 
'BEGIN TRANSACTION' statement if appropriate for the type of DB).  In cases 
where holding long read locks might be an issue, this should be resolved by 
either 1) using the appropriate clause as part of the SELECT, 2) a 'SET 
ISOLATION' statement beforehand, or 3) using a separate connection 
configured for a different isolation level.

These workarounds for the "long select read lock" problem should all be 
adequate for the situations Ty and I have encountered where it occurs, and 
they do not impose an arbitrary burden on users of MVCC databases such as 
PostgreSQL and Oracle.

If there are no objections, then when I change the transaction-join 
behavior I will also remove the "joinTxn" binding from the SQLCursor class, 
so that using the 'joinTxn' flag will cause an error, thus conveniently 
showing you where to remove this un-PEAKish wart from your code.  :)