handle transaction restarts

[nstewart]

sqlalchemy.exc.OperationalError: (psycopg2.extensions.TransactionRollbackError) restart transaction: HandledRetryableTxnError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE): "sql txn" id=abae817b key=/Table/53/1/"new york"/"\xf6\x17\xfaw\xeeaKY\x86H\x8e\x86\x00>7\xff"/0 rw=true pri=0.05009593 iso=SERIALIZABLE stat=PENDING epo=0 ts=1529439347.288508133,3 orig=1529439347.288508133,0 max=1529439347.288508133,0 wto=false rop=false seq=13

[nstewart]

cockroachdb/docs#3285

[nstewart]

ran into this: cockroachdb/sqlalchemy-cockroachdb#25

also

sqlalchemy.exc.InternalError: (psycopg2.InternalError) SAVEPOINT COCKROACH_RESTART needs to be the first statement in a transaction

and sqlalchemy.exc.InternalError: (psycopg2.InternalError) current transaction is committed, commands ignored until end of transaction block

SQL alchemy is creating implicit transactions that seem to be messing things up. Will workaround, but noting for future pm investigation.

Example implicit txn

2018-06-21 17:24:51,877 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-06-21 17:24:51,878 INFO sqlalchemy.engine.base.Engine SELECT rides.id AS rides_id, rides.city AS rides_city, rides.vehicle_city AS rides_vehicle_city, rides.rider_id AS rides_rider_id, rides.vehicle_id AS rides_vehicle_id, rides.start_address AS rides_start_address, rides.end_address AS rides_end_address, rides.start_time AS rides_start_time, rides.end_time AS rides_end_time, rides.revenue AS rides_revenue
FROM rides
WHERE rides.city = %(param_1)s AND rides.id = %(param_2)s
2018-06-21 17:24:51,878 INFO sqlalchemy.engine.base.Engine {'param_1': 'new york', 'param_2': UUID('248095ce-0be7-48e1-8153-9d868142e1b9')}
<Ride(city='new york', id='248095ce-0be7-48e1-8153-9d868142e1b9', rider_id='216120ce-50bc-427d-9e1f-d388c7f0ce83', vehicle_id='dc41fb6e-6258-4d1f-9a4b-0855c10a9c4b')>

Will work with docs

[nstewart]

I'm going to remove savepoints. Not sure the implications on performance but they seem to be pretty incompatible with sqlalchemys implicit txns

[nstewart]

cockroachdb/cockroach#5935

[nstewart]

Need to figure out right error code. Before I was checking for SERIALIZABLE retries but it looks like there is another class of retryable errors

(psycopg2.extensions.TransactionRollbackError) restart transaction: HandledRetryableTxnError: TransactionRetryError: retry txn (RETRY_WRITE_TOO_OLD): "sql txn" id=eea47d96 key=/Table/61/1/"new york"/"^O$\xeb\xee9NJ\x80a\xd9\xf7bX\x92\x13"/0 rw=true pri=0.01842827 iso=SERIALIZABLE stat=PENDING epo=0 ts=1529660166.110102129,2 orig=1529660166.110102129,0 max=1529660166.110102129,0 wto=true rop=false seq=12

cockroachdb/docs#1587

[nstewart]

https://stackoverflow.com/questions/27351433/sqlalchemy-serializable-transactions-isolation-and-retries-in-idiomatic-python