Mysql table lock instead of row locks

Question

Mysql (5.5) Innodb in this certain case is putting table lock rather than row locks.

And this is causing failure of other insert queries to the table. Also this is a part of a larger transaction.

Insert into table x(x1,x2)
Select y1,y2 from y
where 'big sql case based conditions'

Now the select query select only part of table (based on which user) and not full table.

But mysql innodb is putting table locks.

Is there any way I can avoid this? Any help will be appreciated.

Jason Heo Jason Heo · Accepted Answer · 2013-12-02T14:24:47

I think you are using tx in REPEATABLE READ mode. could you check out this?

mysql> show session variables like '%isol%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

If so, change it to 'READ COMMITTED' like this:

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like '%isol%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+

Then, client A starts INSERT INTO .. SELECT and insert a row from client B. I think client B's INSERT would succeed.

Mysql table lock instead of row locks

1 Answers