getting locks on table due to select queries?

Question

we are using below select queries from long time. But today we are receiving many locks on database.

Please help me how to resolve the locks due to select queries.

the table size is very small 300kb.

we optimized table but no luck

query info and table structure from below.

Req-SQL:[select max(fullname) from prod_sets where name='view_v01' for update] 
Req-Time: 5 sec 
Blocker-SQL:[] 
Blocker-Command:[Sleep] 
Blocker-Time: 73 sec

Req-SQL:[select max(fullname) from prod_sets where name='view_v01' for update] 
Req-Time: 22 sec 
Blocker-SQL:[] 
Blocker-Command:[Sleep] 
Blocker-Time: 73 sec

CREATE TABLE `prod_sets` (
  `modified` datetime DEFAULT NULL,
  `create` datetime DEFAULT NULL,
  `name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `fullname` decimal(12,0) DEFAULT NULL,
  UNIQUE KEY `idx_n` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Explain Plan:

mysql> explain select max(fullname) from prod_sets where name='view_v01' for update;
+----+-------------+---------------+-------+---------------+----------+---------+-------+------+-------+
| id | select_type | table         | type  | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+---------------+-------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | prod_sets     | const | idx_name      | idx_name | 53      | const |    1 |       |
+----+-------------+---------------+-------+---------------+----------+---------+-------+------+-------+
1 row in set (0.01 sec)

Answer 1

Assuming you know what FOR UPDATE means. Is there any reason name is DEFAULT NULL? If not, I would like to make name to PRIMARY KEY. Innodb's PK is clustered, so it makes access fullname faster

CREATE TABLE `prod_sets` (
  `modified` datetime DEFAULT NULL,
  `create` datetime DEFAULT NULL,
  `name` varchar(50) COLLATE latin1_bin DEFAULT NOT NULL,
  `fullname` decimal(12,0) DEFAULT NULL,
  PRIMARY KEY `idx_n` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Or simply add following INDEX.

ALTER TABLE prod_sets ADD INDEX(name, fullname);

Answer 2

If you are locking some rows of a table then you must explicitly unlock the table after your work has been done.

use: UNLOCK TABLES;

or use : kill put_process_id_here;

refer these links for further reading

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

http://lmorgado.com/blog/2008/09/10/mysql-locks-and-a-bit-of-the-query-cache/