maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

good question, at least here the implicity commit is with LOCK table and
not unlock table

CREATE OR REPLACE TABLE m (c INT) ENGINE = InnoDB;
CREATE OR REPLACE TABLE t (c INT) ENGINE = myisam; (must be nontransaction
table)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
SELECT @@in_transaction; <- return 1
lock table t write;
SELECT @@in_transaction; <- return 0
COMMIT;

BEGIN;
INSERT INTO m VALUES (1);
SELECT @@in_transaction; <- return 1
lock table t write;
SELECT @@in_transaction; <- return 0
INSERT INTO t VALUES (1);
SELECT @@in_transaction; <- return 0
COMMIT;


2014-09-22 16:44 GMT-03:00 Federico Razzoli <federico_raz@xxxxxxxx>:

> In this MySQL manual page:
> http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
>
> I read:
> "UNLOCK TABLES commits a transaction only if any tables currently have
> been locked with LOCK TABLES to acquire nontransactional table locks."
>
> However, this doesn't seem to be the case, at least with MariaDB 10.0:
>
> MariaDB [test]> CREATE OR REPLACE TABLE m (c INT) ENGINE = InnoDB;
> Query OK, 0 rows affected (0.39 sec)
>
> MariaDB [test]> CREATE OR REPLACE TABLE t (c INT UNIQUE) ENGINE = InnoDB;
> Query OK, 0 rows affected (0.54 sec)
>
> MariaDB [test]> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> Query OK, 0 rows affected (0.00 sec)
>
> MariaDB [test]> START TRANSACTION;
> Query OK, 0 rows affected (0.00 sec)
>
> MariaDB [test]> INSERT INTO m VALUES (1);
> Query OK, 1 row affected (0.00 sec)
>
> MariaDB [test]> INSERT INTO t VALUES (1);
> Query OK, 1 row affected (0.00 sec)
>
> MariaDB [test]> UNLOCK TABLES;
> Query OK, 0 rows affected (0.00 sec)
>
> MariaDB [test]> SELECT @@in_transaction;
> +------------------+
> | @@in_transaction |
> +------------------+
> |                1 |
> +------------------+
> 1 row in set (0.00 sec)
>
> --
> So, could you please confirm that UNLOCK TABLES is transaction-safe? If it
> isn't, could someone please explain in which cases it isn't?
>
> Regards
> Federico
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>



-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle