This executes Goal as a database transaction. This predicate is only useful if the database supports transactions (i.e. changes are local until committed). If Goal succeeds session_transaction/2 commits the update, cuts any alternative solutions to Goal and succeeds itself.
If Goal fails or causes a database error, session_transaction/2 fails and rolls back any changes done to the database.
If Goal aborts, the update is rolled back, and session_transaction/2 aborts.
MySQL specific note: transactions are supported for transactional tables only (i.e. stored with a storage engine with transaction-safe capabilities).
Calls of this predicate can be nested, but only the outermost transaction is real. All the inner transactions are simply equivalent to call(Goal). This way it is possible to write a call to session_transaction/2, into some code that implements a simple update, but then to include that simple update into a larger transaction.
Similarly session_commit/1 and session_rollback/1 alter their behaviour so that they can be used within a transaction.
Transactions are local to one session so there is no way to safely make an update relating to several sessions.
transfer(Session, Amount,FromAccount,ToAccount) :-
session_transaction(Session,
transfer_(Session,Amount,FromAccount,ToAccount)
).
% note '?' in SQL in the syntax MySQL uses for placeholders. This may be
% different in other DBMS
transfer_(Session, Amount, FromAccount, ToAccount) :-
SQL = "update accounts set balance = balance + ? \
where id = ?",
Deduct is - Amount,
session_sql_prepare(Session,incbal(1.0,12),SQL,1,Update),
cursor_next_execute(Update,incbal(Deduct,FromAccount)),
cursor_next_execute(Update,incbal(Amount,ToAccount)).