MySQL Forums
Forum List  »  InnoDB

innodb does table lock instead row lock
Posted by: andrew polkowski
Date: December 06, 2008 09:13AM

I use phpbb with myisam but receive too many table locks so i changed topics/users table to innodb.
But as you can see below, when topic table (innodb) get updated SET topic_views = topic_views + 3 all other queries which involves select t.* (topic table) are locked. Why? Row level locking should eliminate this locks right?

What can i do to row level locking start to work?

| 2953120 | slave | ip | as | Query | 0 | Locked | UPDATE phpbb_topics
SET topic_views = topic_views + 3
WHERE topic_id = 987430 |
| 2953121 | slave | ip | as | Query | 0 | Sending data | SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post |
| 2953123 | slave | ip | as | Query | 0 | Locked | SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post |
| 2953124 | slave | ip | as | Query | 0 | Writing to net | SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_icq, u.user_aim, u.u | |
| 2953128 | slave | ip | as | Query | 0 | end | UPDATE phpbb_users
SET user_session_time = 1228575971, user_session_page = -2
WHERE u |
| 2953130 | slave | ip | as | Query | 0 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 2953131 | slave | ip | as | Query | 0 | end | UPDATE phpbb_users
SET user_session_time = 1228575971, user_session_page = -2
WHERE u |
| 2953132 | slave | ip | as | Query | 0 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 2953133 | slave | ip | as | Query | 0 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 2953134 | slave | ip | as | Query | 0 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 2953135 | slave | ip | as | Query | 0 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |

ps half tables are innodb (frequently updated) rest is still myisam



Edited 2 time(s). Last edit at 12/06/2008 09:17AM by andrew polkowski.

Options: ReplyQuote


Subject
Views
Written By
Posted
innodb does table lock instead row lock
3393
December 06, 2008 09:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.