maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

LOCK always commits current transaction. This is documented. But I wasn't able to commit a transaction with UNLOCK. Perhaps I'm missing something?

Federico


--------------------------------------------
Lun 22/9/14, Roberto Spadim <roberto@xxxxxxxxxxxxx> ha scritto:

 Oggetto: Re: [Maria-discuss] transactions and UNLOCK TABLES
 A: "Federico Razzoli" <federico_raz@xxxxxxxx>
 Cc: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 Data: Lunedì 22 settembre 2014, 22:10
 
 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 1lock table t
 write;SELECT @@in_transaction; <- return
 0COMMIT;
 BEGIN;INSERT
 INTO m VALUES (1);SELECT @@in_transaction; <-
 return 1lock table t write;SELECT
 @@in_transaction; <- return 0INSERT INTO t
 VALUES (1);SELECT @@in_transaction; <- return
 0COMMIT;
 
 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
 SPAEmpresarialEng. Automação e
 Controle