Added mechanism so that begin, commit, rollback can be used outside of with-transaction

[beberman]

There is sometimes a need to push the semantics of transactions to higher levels in a code stack. In particular in my code there is a an object cache and then an interface on that cache that sits above the underlying database driver code. This cache has a need to wrap transactions around multiple connections to multiple schemas. Further some of the code supports database transactions and other code does not. To make this polymorphic the being/ commit/ rollback code needs to work outside of the macro with-transaction. As noted in a prior note the current code only supports transactions within with-transaction or with-savepoint. The core in this refactor is the global dynamic variable transaction-state which is used throughout the driver code through the calls to in-transaction and get-transaction-state. Its set within a dynamic let context in with-savepoint and %with-transaction. This doesn't work if the semantics are separated. Therefore I had to introduce a start-transaction and end-transaction function to set things outside of these macros. End-transaction calls an internal function remove-transaction-state. I suspect for multi-threading the operations on transaction-state need to be protected with a thread lock. Finally I added clear-transaction-state to enable debugging cleanup or server issue resolution.

There are probably better ways to support this functionality. The code was fairly subtle.

[fukamachi]

I like the idea, and adding the global version of those functions is fine to me. Is this code still in progress?

[beberman]

Yes the code for implementing the capability is in the pull request. I've tested it outside of this code base. The following construction works:

(defun test-fn (connection) (let* ((stmt "Insert into test ("id", "values") values (1, 'foo')") (prepared-statement (data-api::db-prepare connection stmt)) (setf ok nil) )

(setf (slot-value 'dbi::auto-commit connection) nil)
(unwind-protect
     (progn
       (start-transaction connection)
       (data-api::db-execute connection stmt)
       (error "error")
       )
  (progn
    (if (not ok)
        (dbi::rollback connection)
        (dbi::commit connection))
    (dbi::end-transaction connection)
    ))
))

On Mon, May 13, 2024 at 3:21 AM Eitaro Fukamachi @.***> wrote:

I like the idea, and adding the global version of those functions is fine to me. Is this code still in progress?

— Reply to this email directly, view it on GitHub https://github.com/fukamachi/cl-dbi/pull/84#issuecomment-2106832605, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACM4YURIRYWRBX4RUOYCA2TZCBSWPAVCNFSM6AAAAABGU6LC4OVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBWHAZTENRQGU . You are receiving this because you authored the thread.Message ID: @.***>

[beberman]

I found a potential issue that I am unable to find a clear work around. The issue is that if there are nested transactions when an error occurs within a savepoint the code for mysql has an error handler macro called with-error-handler. This code calls cl-mysql-system:releasewhich ultimately does a return-or-close. What I see happening in the traces is that when an error occurs releasegets called twice. It only gets called once on a successful insert/update. The net is that when the code goes to rollback a savepoint the savepoint is in the list within the transaction-state code but MySQL no longer knows about that savepoint so it through the error "DB Error: SAVEPOINT savepoint does not exist (Code: 1305)". I think what is happening here is that the connection has been closed which results in the transaction being closed and automatically rolledback, thus clearing all the savepoints. I've been unable to determine how to adjust the error handler to handle this issue.