[Maria-discuss] transactions and UNLOCK TABLES
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
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@yahoo.it>:
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
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@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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@yahoo.it>: 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Roberto Spadim SPAEmpresarialEng. Automação e Controle
this don't work too: LOCK TABLE t WRITE; BEGIN; SELECT @@in_transaction; (return 1) UNLOCK TABLES; SELECT @@in_transaction; (return 1) maybe a wrong documentation 'bug'
better explained at mariadb kb https://mariadb.com/kb/en/mariadb/documentation/sql-commands/transactions/lo...
No, becuase the first test case I've posted shows that the reality is different... :) I'm just asking to explain the exact interaction between trx and UNLOCK, because I need to know if statements I use are safe. Federico -------------------------------------------- Mar 23/9/14, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net>, "maria-docs" <maria-docs@lists.launchpad.net> Data: Martedì 23 settembre 2014, 00:14 better explained at mariadb kbhttps://mariadb.com/kb/en/mariadb/documentation/sql-commands/transactions/lo...
well from kb what i understand is: to use unlock with innodb (the engine you use with both tables), you should SET innodb_table_locks=1; SET autocommit=0; after that any LOCK/UNLOCK with innodb tables inside a transaction will commit the transaction try again using innodb_table_locks, and autocommit variables 2014-09-22 19:51 GMT-03:00 Federico Razzoli <federico_raz@yahoo.it>:
No, becuase the first test case I've posted shows that the reality is different... :) I'm just asking to explain the exact interaction between trx and UNLOCK, because I need to know if statements I use are safe.
Federico
-------------------------------------------- Mar 23/9/14, Roberto Spadim <roberto@spadim.com.br> ha scritto:
Oggetto: Re: [Maria-discuss] transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net>, "maria-docs" < maria-docs@lists.launchpad.net> Data: Martedì 23 settembre 2014, 00:14
better explained at mariadb kbhttps:// mariadb.com/kb/en/mariadb/documentation/sql-commands/transactions/lock-tables-and-unlock-tables/#limitations
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
SET innodb_table_locks=1; SET autocommit=0; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; INSERT INTO m VALUES (1); INSERT INTO t VALUES (1); SELECT @@in_transaction; <- return 1 UNLOCK TABLES; SELECT @@in_transaction; <- return 1 ------ SET innodb_table_locks=1; SET autocommit=0; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; SELECT @@in_transaction; <- return 1 lock table m write; SELECT @@in_transaction; <- return 1 INSERT INTO m VALUES (1); INSERT INTO t VALUES (1); /* Erro SQL (1100): Table 't' was not locked with LOCK TABLES */ SELECT @@in_transaction; <- return 1 UNLOCK TABLES; SELECT @@in_transaction; <- return 0 2014-09-22 20:46 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:
well from kb what i understand is: to use unlock with innodb (the engine you use with both tables), you should SET innodb_table_locks=1; SET autocommit=0; after that any LOCK/UNLOCK with innodb tables inside a transaction will commit the transaction
try again using innodb_table_locks, and autocommit variables
2014-09-22 19:51 GMT-03:00 Federico Razzoli <federico_raz@yahoo.it>:
No, becuase the first test case I've posted shows that the reality is
different... :) I'm just asking to explain the exact interaction between trx and UNLOCK, because I need to know if statements I use are safe.
Federico
-------------------------------------------- Mar 23/9/14, Roberto Spadim <roberto@spadim.com.br> ha scritto:
Oggetto: Re: [Maria-discuss] transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net>, "maria-docs" < maria-docs@lists.launchpad.net> Data: Martedì 23 settembre 2014, 00:14
better explained at mariadb kbhttps:// mariadb.com/kb/en/mariadb/documentation/sql-commands/transactions/lock-tables-and-unlock-tables/#limitations
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
I would never use LOCK TABLES on InnoDB, look again my first test case and you'll see that it's different from what documentation says :) Federico -------------------------------------------- Mar 23/9/14, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Martedì 23 settembre 2014, 01:46 well from kb what i understand is:to use unlock with innodb (the engine you use with both tables), you should SET innodb_table_locks=1; SET autocommit=0; after that any LOCK/UNLOCK with innodb tables inside a transaction will commit the transaction try again using innodb_table_locks, and autocommit variables 2014-09-22 19:51 GMT-03:00 Federico Razzoli <federico_raz@yahoo.it>: No, becuase the first test case I've posted shows that the reality is different... :) I'm just asking to explain the exact interaction between trx and UNLOCK, because I need to know if statements I use are safe. Federico -------------------------------------------- Mar 23/9/14, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net>, "maria-docs" <maria-docs@lists.launchpad.net> Data: Martedì 23 settembre 2014, 00:14 better explained at mariadb kbhttps://mariadb.com/kb/en/mariadb/documentation/sql-commands/transactions/lo... _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -- Roberto Spadim SPAEmpresarialEng. Automação e Controle
Let's make the snippet simpler. Only one table, non-transactional, please look at the difference from what docs say and the real behaviour. If one could explain what UNLOCK TABLES exactly does, it would be great. MariaDB [test]> SELECT @@in_transaction, @@autocommit; +------------------+--------------+ | @@in_transaction | @@autocommit | +------------------+--------------+ | 0 | 1 | +------------------+--------------+ 1 row in set (0.00 sec) MariaDB [test]> CREATE OR REPLACE TABLE t (c INT) ENGINE = MEMORY; Query OK, 0 rows affected (0.12 sec) MariaDB [test]> LOCK TABLE t WRITE; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> INSERT INTO t VALUES (1); Query OK, 1 row affected (0.01 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) Regards Federico
Hi Federico, On 23.09.2014 12:50, Federico Razzoli wrote:
Let's make the snippet simpler. Only one table, non-transactional, please look at the difference from what docs say and the real behaviour. If one could explain what UNLOCK TABLES exactly does, it would be great.
MariaDB [test]> SELECT @@in_transaction, @@autocommit; +------------------+--------------+ | @@in_transaction | @@autocommit | +------------------+--------------+ | 0 | 1 | +------------------+--------------+ 1 row in set (0.00 sec)
MariaDB [test]> CREATE OR REPLACE TABLE t (c INT) ENGINE = MEMORY; Query OK, 0 rows affected (0.12 sec)
MariaDB [test]> LOCK TABLE t WRITE; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
"Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES." http://dev.mysql.com/doc/refman/5.5/en/commit.html
MariaDB [test]> INSERT INTO t VALUES (1); Query OK, 1 row affected (0.01 sec)
MariaDB [test]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
So, this UNLOCK is not actually doing anything. Regards, Elena
MariaDB [test]> SELECT @@in_transaction; +------------------+ | @@in_transaction | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)
Regards Federico
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thank you for your clarification, Elena. Based on this information, I find the MySQL manual confusing, so I have reported a bug: http://bugs.mysql.com/bug.php?id=74033 Unless it turns out that I'm still missing something, I will also check the kb. Regards Federico -------------------------------------------- Mar 23/9/14, Elena Stepanova <elenst@montyprogram.com> ha scritto: Oggetto: Re: [Maria-discuss] R: transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it>, maria-discuss@lists.launchpad.net Data: Martedì 23 settembre 2014, 11:51 Hi Federico, On 23.09.2014 12:50, Federico Razzoli wrote:
Let's make the snippet simpler. Only one table, non-transactional, please look at the difference from what docs say and the real behaviour. If one could explain what UNLOCK TABLES exactly does, it would be great.
MariaDB [test]> SELECT @@in_transaction, @@autocommit;
+------------------+--------------+
| @@in_transaction | @@autocommit |
+------------------+--------------+
| 0 | 1 | +------------------+--------------+ 1 row in set (0.00 sec)
MariaDB [test]> CREATE OR REPLACE TABLE t (c INT) ENGINE = MEMORY; Query OK, 0 rows affected (0.12 sec)
MariaDB [test]> LOCK TABLE t WRITE; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
"Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES." http://dev.mysql.com/doc/refman/5.5/en/commit.html
MariaDB [test]> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
So, this UNLOCK is not actually doing anything. Regards, Elena
MariaDB [test]> SELECT
@@in_transaction;
+------------------+
| @@in_transaction | +------------------+ | 1 | +------------------+
1 row in set (0.00 sec)
Regards Federico
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
nice, i will include the information about lock innodb tables to your bug request just to help a better explain at mysql docs 2014-09-23 8:50 GMT-03:00 Federico Razzoli <federico_raz@yahoo.it>:
Thank you for your clarification, Elena. Based on this information, I find the MySQL manual confusing, so I have reported a bug:
http://bugs.mysql.com/bug.php?id=74033
Unless it turns out that I'm still missing something, I will also check the kb.
Regards Federico
-------------------------------------------- Mar 23/9/14, Elena Stepanova <elenst@montyprogram.com> ha scritto:
Oggetto: Re: [Maria-discuss] R: transactions and UNLOCK TABLES A: "Federico Razzoli" <federico_raz@yahoo.it>, maria-discuss@lists.launchpad.net Data: Martedì 23 settembre 2014, 11:51
Hi Federico,
On 23.09.2014 12:50, Federico Razzoli wrote:
Let's make the snippet simpler. Only one table, non-transactional, please look at the difference from what docs say and the real behaviour. If one could explain what UNLOCK TABLES exactly does, it would be great.
MariaDB [test]> SELECT @@in_transaction, @@autocommit;
+------------------+--------------+
| @@in_transaction | @@autocommit |
+------------------+--------------+
| 0 | 1 | +------------------+--------------+ 1 row in set (0.00 sec)
MariaDB [test]> CREATE OR REPLACE TABLE t (c INT) ENGINE = MEMORY; Query OK, 0 rows affected (0.12 sec)
MariaDB [test]> LOCK TABLE t WRITE; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
"Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES."
http://dev.mysql.com/doc/refman/5.5/en/commit.html
MariaDB [test]> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
So, this UNLOCK is not actually doing anything.
Regards, Elena
MariaDB [test]> SELECT
@@in_transaction;
+------------------+
| @@in_transaction | +------------------+ | 1 | +------------------+
1 row in set (0.00 sec)
Regards Federico
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
+1 =) mysql manual sometimes don't help, that's why i really prefer mariadb kb i think that many years ago mysql implement this nontransactional like locks to innodb just to allow guys porting from myisam to innodb using old apps without rewriting the lock/unlock to begin/commit if you are using innodb as transactional you will never use lock/unlock, in my opnion maybe you probably only use locks at filesystem or use get_lock() mysql function or any other IPC function, but be carefull about scalability i don't know what's the 'best' lock server today, all shared locks that i use today i use over a filesystem and network (nfs/smb/others network file system), if get_lock work with galera maybe i could get a try 2014-09-23 6:51 GMT-03:00 Elena Stepanova <elenst@montyprogram.com>:
Hi Federico,
On 23.09.2014 12:50, Federico Razzoli wrote:
Let's make the snippet simpler. Only one table, non-transactional, please look at the difference from what docs say and the real behaviour. If one could explain what UNLOCK TABLES exactly does, it would be great.
MariaDB [test]> SELECT @@in_transaction, @@autocommit; +------------------+--------------+ | @@in_transaction | @@autocommit | +------------------+--------------+ | 0 | 1 | +------------------+--------------+ 1 row in set (0.00 sec)
MariaDB [test]> CREATE OR REPLACE TABLE t (c INT) ENGINE = MEMORY; Query OK, 0 rows affected (0.12 sec)
MariaDB [test]> LOCK TABLE t WRITE; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
"Beginning a transaction causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES."
http://dev.mysql.com/doc/refman/5.5/en/commit.html
MariaDB [test]> INSERT INTO t VALUES (1); Query OK, 1 row affected (0.01 sec)
MariaDB [test]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
So, this UNLOCK is not actually doing anything.
Regards, Elena
MariaDB [test]> SELECT @@in_transaction; +------------------+ | @@in_transaction | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)
Regards Federico
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
participants (3)
-
Elena Stepanova -
Federico Razzoli -
Roberto Spadim