[Maria-discuss] R: transactions and UNLOCK TABLES
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 ___ 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
Re: [Maria-discuss] R: transactions and UNLOCK TABLES
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
Re: [Maria-discuss] R: transactions and UNLOCK TABLES
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 ele...@montyprogram.com ha scritto: Oggetto: Re: [Maria-discuss] R: transactions and UNLOCK TABLES A: Federico Razzoli federico_...@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
Re: [Maria-discuss] R: transactions and UNLOCK TABLES
+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 ele...@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 ___ 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
Re: [Maria-discuss] R: transactions and UNLOCK TABLES
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_...@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 ele...@montyprogram.com ha scritto: Oggetto: Re: [Maria-discuss] R: transactions and UNLOCK TABLES A: Federico Razzoli federico_...@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 ___ 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
