472,353 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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 3131

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Steve McWilliams | last post by:
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...
by: jayson_13 | last post by:
Hi, I need to implement a counter and i face problem of locking so hope that u guys can help me. I try to do test like this : 1st...
by: Reuben Pearse | last post by:
Hi all, I've just converted the tables in a big database (approx 27 million records) from MyISAM to InnoDB. When I was using MyISAM I backed up...
by: Puvendran | last post by:
Hi, We have encountered deadlock on a table which is used to generate sequential numbers for different categories eg typical entries Category ...
by: Trent | last post by:
Hello, all. I have the following production DB2 environment. DB2 8.1.4 (fp4) WG edition with 2 production databases on Windows 2003 standard...
by: Peter | last post by:
(I've tried this Q in the ms forums without response. See how it goes here.) Using A2003 but I guess this is not version-specific... If I...
by: Good Man | last post by:
Hi there I have a database with about 20 or so tables, maybe a few thousand rows in each. I am starting to do more complex things with my...
by: shakahshakah | last post by:
Just started investigating InnoDB after having used MyISAM tables in the 4.0.12 version, almost immediately ran into a locking issue with INSERTs,...
by: e_matthes | last post by:
Hello everyone, I have read many threads about concurrency issues, and I think I understand some of the pieces, but not the whole picture. I...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.