Mysql table lock instead of row locks
NickName:amitchhajer Ask DateTime:2013-12-02T13:23:35

Mysql table lock instead of row locks

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.

Copyright Notice:Content Author:「amitchhajer」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/20321407/mysql-table-lock-instead-of-row-locks

More about “Mysql table lock instead of row locks” related questions

Mysql table lock instead of row locks

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.

Show Detail

Difference between table and row locks

I'm studying about MySQL and how it works, and something confuses me and I don't find any clear explanation on the web about this. What exactly is the difference between row and table locks? One ...

Show Detail

Will a table lock prevent row locking

I have a table with more than 160 milion of entries, which has a wrong table engine, so I'm going to change the engine. When I do this without any preparation, I get a error due to my buffer size,

Show Detail

How do table locks and row locks interact with each other in PostgreSQL?

I understand how table locks interact with other table locks and how row locks interact with other row locks, but I don't fully understand how table locks interact with row locks. For instance pro...

Show Detail

Failure obtaining db row lock: No row exists in table QRTZ_LOCKS for lock named: TRIGGER_ACCESS

I'm new to Quartz scheduler and I'm using it with spring ... When I tried to run the XML file that contains Quartz configuration I got an exception stating that: Failure obtaining db row lock: No...

Show Detail

the total number of locks exceeds lock table size , when using mysql delete commnad

I am using mysql server v5.1.73 on a Centos 6.4 64bit operating system.I have a table with about 17m records that it's size is about 10GB. mysql engine for this table is innodb. This table has 10 c...

Show Detail

MySQL lock compatibility

According to the official docs from here: the lock compatibility matrix: X IX S IS X Conflict Conflict Conflict Conflict IX Conflict Compatible

Show Detail

MySQL InnoDB gap locks

my table as follow: Create Table: CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, KEY `idx_a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 all datas as follow: +------+ | a | +------

Show Detail

Do table locks scale? / Would row locks be more efficient for nested sets?

I'm using nested sets to store hierarchical data in a MyISAM table; the table consists of several hierarchical sets for each user. Each user will be the only one writing to his respective trees, but

Show Detail

LOCK IN SHARE MODE locks entire table

Documentation: SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of...

Show Detail