We have noted some interesting behaviour using TransactionScope that'd we'd like to clarify.
Basically we are seeing every transaction being committed as a prepared transaction even if all the activity within TransactionScope was on a single connection, even if just a single write for example.
Code: Select all
2014-12-09 15:38:25 EST LOG: statement: BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED 2014-12-09 15:38:25 EST LOG: execute PRSTMT19624403211663369718: UPDATE namespacename.table SET column = $1 WHERE id = $2 2014-12-09 15:38:25 EST DETAIL: parameters: $1 = 'f', $2 = '3bcf0062-eadb-11e3-9ef5-f7d01a999061' 2014-12-09 15:38:25 EST LOG: statement: PREPARE TRANSACTION '1555c823-daa3-42b5-b5e2-42f06aa5e3e2' 2014-12-09 15:38:25 EST LOG: statement: COMMIT PREPARED '1555c823-daa3-42b5-b5e2-42f06aa5e3e2'
Additionally, rarely (but of high impact when it does happen), we have seen a prepared transactions being created but not committed, leaving a table locked until we manually release the prepared transaction. Not really sure how this could be happening given the PREPARE TRANSACTION and COMMIT PREPARED all spit out at once.
Basically we then start piling up connections til the entire db becomes locked.
I'm aware that Transaction Scope Local=true; will turn off prepared transactions with TransactionScope, but we do have a few instances in the future that we will want them. It seems odd that TransactionScope is so coupled to prepared transactions. Is this behaviour expected?
Also any ideas how we would potentially end up with an orphaned prepared transaction given the above simple update?