Hello, 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 703-691-0433x21 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
