innodb does table lock instead row lock
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.