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