MySQL Bugs: #40495: expose information if the session is currently in a transaction

Bug #40495	expose information if the session is currently in a transaction
Submitted:	4 Nov 2008 11:56
Reporter:	Axel Schwenke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There is no (official) way to tell if a certain session (connection) is currently in a transaction. The information is however maintained in the server and sent as bit SERVER_STATUS_IN_TRANS in the server status word. But there is no way to access this information with an API function.

How to repeat:
n/a

Suggested fix:
There are two ways to implement this and probably we should implement both:

a) add API functions to the connectors to expose the server status word from the last response. The mysql_insert_id() does something similar for the last insert id (this is also sent in the server response packet). The MYSQL struct in the C API already stores the server status in the server_status component (see attached test program). We just need to expose this data in a safe way.

b) add a read only session variable for this. I.e. @@in_transaction. Again there is already a similar session variable @@last_insert_id.

Example showing how to access mysql->server_status

Attachment: csc30924.c (text/x-csrc), 1.02 KiB.

Rationale why two solutions and why to implement both:

a) API function: this has the advantage that the current transaction status can be told without a roundtrip to the server

b) session variable: is the most transparent solution, works for any connector and also in stored routines.

if we decide to implemt only one, then b) would be favorable

Rationale:

If you write a stored procedure to do a particular task you may want it to be able to be called on it's own (from the command line). If the task is complex it may require several "steps" and thus need to be called inside a transaction so all or none of the changes take place.

In this particular case you would need to wrap the transaction inside the stored procedure, adding BEGIN WORK, and COMMIT/ROLLBACK as appropriate.

Now consider that this stored procedure is part of a multi-statement command line "transaction". That is it is called from within an already running transaction. In this case you need to check if you are already inside a transaction and NOT perform the BEGIN WORK as it would commit the currently incomplete transaction.  However if something fails you need to perform the ROLLBACK. If things work fine you EXPECT the COMMIT to be done by the caller of the stored procedure.

In situations like this it is vital to know if we are inside a transaction to adjust the procedures behaviour. Other RDBMS do provide this information.

The same issue applies if the stored procedure is called from within a trigger.

This current limitation prevents users from building more complex stored procedures.

https://dev.mysql.com/worklog/task/?id=6631