如果我的连接已经启动了一个事务，有没有办法查询mySql？

Question

我找到了许多关于框架如何处理事务嵌套的分析器，但我找不到是否存在针对mySql RDMS的可执行查询，该查询将返回事务是否已启动。这是可能的吗？谢谢

Answer 1

这有点棘手，但您可以在http://forge.mysql.com/tools/tool.php?id=145上找到一个如何做到这一点的示例

Answer 2

我在这里复制@wonk0链接，因为它不再是可避免的。

DELIMITER $$

CREATE PROCEDURE `myexample` (
  OUT errno    INT,
  OUT error    VARCHAR(255)
)
BEGIN

  DECLARE need_to_commit BOOL DEFAULT FALSE;

  main:BEGIN

    -- for example, catch duplicate key errors and roll back
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
      ROLLBACK TO SAVEPOINT myexample;
      SET errno = 1060,
          error = 'Duplicate key.';
    END;

    -- catch any other errors that should cause automatic rollbacks

    -- ------
    -- set up the savepoint / trx handler
    -- 

    DECLARE CONTINUE HANDLER FOR 1305
    BEGIN
      START TRANSACTION;
      SET need_to_commit = TRUE;
    END;

    -- this will have no effect if we are not in a trx
    SAVEPOINT myexample;

    -- this will error if we are not in a trx, be caught above, and start a trx
    -- it will do nothing if we are already in a trx
    RELEASE SAVEPOINT myexample;

    -- this will always set a savepoint
    -- because we are now guaranteed to be in a trx
    SAVEPOINT myexample;

    -- 
    -- done setting up savepoint / trx
    -- ------

    -- initialize the OUT parameters
    SET errno = 0,
        error = '';

    -- do some stuff
    INSERT INTO mytable VALUES (1);
    INSERT INTO yourtable VALUES (2);

    -- you can even handle your own errors (without handlers!)
    IF ( 0 != 1 ) THEN
      ROLLBACK TO SAVEPOINT myexample;
      SET errno = 1234,
          error = 'Zero is not one!';
      LEAVE main;
    END IF;

  END; -- main

  -- if we were not in a transaction to start with
  -- we should not leave one dangling, so commit here
  IF need_to_commit THEN 
    COMMIT;
  END IF;

END $$


DELIMITER ;



/*

EXAMPLE

mysql> create table mytable (a int primary key) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> create table yourtable (b int primary key) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> call myexample(@e,@r); select @e,@r;
Query OK, 0 rows affected (0.00 sec)

+------+------------------+
| @e   | @r               |
+------+------------------+
| 1234 | Zero is not one! |
+------+------------------+
1 row in set (0.00 sec)

mysql> select * from mytable union select * from yourtable;
Empty set (0.00 sec)

mysql> insert into yourtable values (2);
Query OK, 1 row affected (0.00 sec)

mysql> call myexample(@e,@r); select @e,@r;
Query OK, 0 rows affected (0.00 sec)

+------+----------------+
| @e   | @r             |
+------+----------------+
| 1060 | Duplicate key. |
+------+----------------+
1 row in set (0.00 sec)

mysql> select * from mytable union select * from yourtable;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

*/