Row level locking with InnoDB

[Kai Ruhnau]

Hi,
I have a quick question, which I did not find answerd by the manual:
Given this sample query:
SELECT COUNT(ID)
FROM mytable
WHERE property=value
FOR UPDATE
Which rows in 'mytable' are locked after this query?
Every row with property=value, because these rows were used to calculate 
COUNT(ID).
No rows, because no row is actually returned / selected.

Thanks and greetings,
Kai Ruhnau
--
This signature is left as an exercise for the reader.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Question about row level locking with InnoDB tables

[Heikki Tuuri]

Steve,

- Original Message - 
From: ""Steve McWilliams"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, August 27, 2003 12:07 AM
Subject: Question about row level locking with InnoDB tables


> 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.

a locking SELECT will set row locks (usually next-key locks) on every index
record it looks at. This is necessary to prevent 'phantom rows' from
appearing in your result set.

If you do not have an index on my_column, MySQL needs to scan the whole
table and thus it locks every row.

http://www.innodb.com/ibman.html#Locks_set_by_statements
http://www.innodb.com/ibman.html#Next_key_locking
http://www.innodb.com/ibman.html#Cope_with_deadlocks

> 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]

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL

Order MySQL technical support from https://order.mysql.com/



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Question about row level locking with InnoDB tables

[Steve McWilliams]

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]