469,609 Members | 1,209 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Question about row level locking with InnoDB tables


I am relatively new to MySql (4.0.14) but I have read through the relevent
documentation and am still confused about how row level locking behaves
with InnoDB tables.

I created a database with a single innodb table which has 2 columns, one
of which is indexed. The locking behavior I see when I test against this
database is that it uses row level locks if the "SELECT ... FOR UPDATE"
involves the indexed column, but uses table level locks if instead it
involves the non-indexed column.

For example, if I have 2 mysql clients that perform the following operations:

client1> set autocommit=0;
client1> begin;
client1> SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE;
client2> set autocommit=0;
client2> begin:
client2> SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE;

The above query by client2 will block if the column in question is not
indexed, implying that client1 has somehow locked the entire table, even
though client1 and client2 are selecting different rows.

Am I misconfiguring something, or does InnoDB simply only support row
level locking when you are selecting indexed rows?

Thanks in advance,

Steve McWilliams
Software Engineer
Emprisa Networks

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 2988

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Steve McWilliams | last post: by
5 posts views Thread by jayson_13 | last post: by
3 posts views Thread by Reuben Pearse | last post: by
12 posts views Thread by Puvendran | last post: by
10 posts views Thread by e_matthes | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.