MySQL Bugs: #2889: UPDATE on condition with non-indexed fields locks whole table

Bug #2889	UPDATE on condition with non-indexed fields locks whole table
Submitted:	19 Feb 2004 4:47	Modified:	19 Feb 2004 10:47
Reporter:	Marat Latypov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.10	OS:	Linux (RedHat 9.0)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
If in UPDATE command in WHERE clause used only non-indexed field it locks whole table.

If in UPDATE command in WHERE clause used one condition with indexed field and second with non-indexed one it locks all records maching first condition.

How to repeat:
TRX 1

begin
update aaa set f1='111' where f2=1 (f2 haven't index)
.....

TRX 2
update aaa set f1='111' where f2=2

Error: 1205 ER_LOCK_WAIT_TIMEOUT Lock wait timeout expired. Transaction was rolled back.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

If a table scan is required to process the query, all rows are locked.  See:

http://www.mysql.com/doc/en/InnoDB_Locks_set.html