Locking Tables in MySQL like in PostgreSQL

0
votes

My Problem is, that I want to insert a new row with a calculated value dependent on a select, executed earlier in the transaction. But avoid, that parallel transactions insert the same calculated value.

This problem should be avoided through transaction isolation level SERIALIZABLE. But it doesn't.

I run through the following steps:

  1. TRANSACTION A: BEGIN;
  2. TRANSACTION B: BEGIN;
  3. TRANSACTION A: select max(x) from tableName;
  4. TRANSACTION B: select max(x) from tableName;
  5. TRANSACTION B: insert into tableName (x, PK_COLUMN) values (max + 1, primaryKeyA);
  6. TRANSACTION A: insert into tableName (x, PK_COLUMN) values (max + 1, primaryKeyB);
  7. TRANSACTION A: COMMIT;
  8. TRANSACTION B: COMMIT;

After this, in MySQL, I have two new rows with the same x value, but I want, that one of both transactions abort.

On PostgreSQL I can do a LOCK TABLE tableName IN SHARE MODE and this prevents TRANSACTION A to execute step 6. On MySQL I tried the LOCKING techniques described under http://dev.mysql.com/doc/refman/5.1/de/lock-tables.html without success.

Any suggestions? Thanks.

1
mysqlsqltransactionslocking

1 Answers

0
votes

I found the Problem. MySQL Workbench does not fork a new transaction for each sql window.... so I was working with the one and only transaction.... After using two terminals connecting to the server. Everything worked as expected.