MySQL Bugs: #3300: UPDATE statement with no index column in where condition locks all rows

Bug #3300	UPDATE statement with no index column in where condition locks all rows
Submitted:	26 Mar 2004 7:30	Modified:	26 Jan 2006 0:57
Reporter:	marcello soffritti
Status:	Closed
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	5.0	OS:	HP/UX (HPUX 11.11)
Assigned to:	Bugs System	Target Version:

Description:
I have two active sessions: session_A and session_B connecting to MySQL
RDBMS using MySQL native API.
session_A does an "update marsof set eta=1 where tipo=1" (tipo doesn't
have an index)
and after session_B does an "update marsof set eta=2 where tipo=2".
With few words, we have two concurrent update involving two different
rows and belonging to the same table.
So, I have seen that session_B update is blocked!!! 
The test succeded if in the where condition I use eta column (an indexed
column).
This is not feasible for my application. 
Is it possibile to solve this problem? It's important for my application that the
expected MySQL lock behaviour is ORACLE like.

How to repeat:
Here it is marsof table:

mysql> desc marsof;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| eta   | int(11) |      | PRI | NULL    | auto_increment |
| tipo  | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

mysql> select * from marsof;
+-----+------+
| eta | tipo |
+-----+------+
|   7 |    7 |
|   8 |    8 |
|  10 |    1 |
|  20 |    2 |
+-----+------+
4 rows in set (0.00 sec)

session_A sql: "update marsof set eta=1 where tipo=1"
session_B sql: "update marsof set eta=2 where tipo=2"

Hi!

To solve this problem satisfactorily we have to wait for the 'row level binlogging'
mechanism to be implemented in MySQL. The work on it should start in Q3 of 2004.

Until then, a new option

innodb_locks_unsafe_for_binlog

that I intend to introduce in 4.1.2 or 4.1.3 will help.

Regards,

Heikki

Hi,
I have tried with mysql RDBMS ver.4.1.2 for HPUX11.11 the option
innodb_lock_unsafe_for_binlog but mysqld doesn't start and traces
the following:
------------------------------------------------------------------
040617 08:41:13  mysqld started
/users/mysql/mysql-debug-4.1.2-alpha-hp-hpux11.11-hppa2.0w/bin/mysqld: ERROR: unknown
variable 'innodb_locks_unsafe_for_binlog'
040617 08:41:13  mysqld ended
------------------------------------------------------------------

I think that MySQL/InnoDB-4.1.2 doesn't actually support the
innodb_lock_unsafe_for_binlog parameter.
Do you?
Best regards,
Marcello.

Marcello,

Jan Lindström has made the necessary patch for innodb_locks_unsafe_for_binlog to 4.1.4:

http://lists.mysql.com/internals/15371

but I still have to review the patch for it to appear in upcoming MySQL-4.1.4.

Best regards,

Heikki

Closing, as this fix was pushed into the 4.1 BK tree on Jul 20th:

ChangeSet@1.1856.170.1, 2004-07-20 14:15:38+03:00, jan@hundin.mysql.fi
  Added innodb_locks_unsafe_for_binlog option. This option turns off Innodb
    next-key locking. Using this option the locks InnoDB sets on index
    records do not affect the ``gap'' before that index record. Thus, this option
    allows phantom problem.

Hi!

I am sorry, there was confusion about in which cases the new option

innodb_locks_unsafe_for_binlog

will help. It will remove 'gap' locks in most cases (except duplicate key checks). Thus,
new inserts to the table to be updated will always succeed. But it will NOT remove the
problem that the rows scanned by the cursor doing the update will remain locked
regardless of whether MySQL updates them or not.

The complete solution would require MySQL to tell to InnoDB whether it really updated a
row or not. That is a bit more difficult, and the implementation will probably wait until
MySQL's row-level binlogging is implemented. After all, this new my.cnf option is not very
beautiful, as you can conclude from its ugly name.

I am changing the status of this feature request to 'open', because a complete solution
will take more time to implement.

For the time being, a partial workaround is to define an index on the table, so that the
rows satisfying the WHERE condition will be found quickly, and no table scan is needed.

Regards,

Heikki

Jan,

can you tell what is the status of this?

Best regards,

Heikki

Hi!

I am working on this right now. Looks like Jan's code does not work if the UPDATE or
DELETE is done through a secondary index.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26609

Marcello,

I have just committed a patch. Next I will test.

I apologize the delay in fixing this bug.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26612

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26613

Hi!

My patches seem to work. Now if you put the option:

innodb_locks_unsafe_for_binlog

in my.cnf in MySQL-5.0.9, then in an UPDATE, if the row does not match the search
criterion, and does not get updated, InnoDB does release the X-lock it took on the
clustered index record, and also on the secondary index record if the search was done
through a secondary index.

Fixed in 5.0.9.

Note that the following inoptimality still remains: if the row would not match, but to
know that the MySQL interpreter has to examine some column that InnoDB should return,
then we have to wait if someone else has a lock on the row. A possible solution would be
to return the row to MySQL even though it is locked, and cancel the waiting lock request
and skip the row if it does not match the search criterion.

Regards,

Heikki

Hi!

Sinisa noticed that the new version does not release the row locks if the PRIMARY KEY
column is updated in the statement. It does release the row locks if an unindexed column
is updated.

If the index that we use in the search to process the UPDATE is updated then MySQL uses
the following algorithm:

1) find the primary key values of all rows that we are going to update, and store them to
a temporary file;
2) fetch and update the rows one-by-one, using that list in the temporary file.

The MySQL code does not call table->file->unlock_row() if the above algorithm is used. It
should call it if the row does not match in 1).

MySQL-5.0 sql_update.cc, about line 329:

      thd->proc_info="Searching rows for update";
      uint tmp_limit= limit;

      while (!(error=info.read_record(&info)) && !thd->killed)
      {
        if (!(select && select->skip_record()))
        {
          table->file->position(table->record[0]);
          if (my_b_write(&tempfile,table->file->ref,
                         table->file->ref_length))
          {
            error=1; /* purecov: inspected */
            break; /* purecov: inspected */
          }
          if (!--limit && using_limit)
          {
            error= -1;
            break;
          }
        }
##### else release the row lock here!
      }

There are probably several places in sql_update.cc, sql_delete.cc, and sql_select.cc
where these row lock releases should be put.

I am now looking at fixing this particular problem.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26909

Hi!

The patch that I submitted a few minutes ago seems to fix this PRIMARY KEY update case.
But there are several other places in MySQL where we should add the call of unlock_row().
The InnoDB side of code is maybe ok.

I apologize not testing the PRIMARY KEY update case when I submitted my patches 10 days
ago.

Since relaxed locking is a major new feature, this should be extensively tested, before
we can say that it is mature.

Regards,

Heikki

Raising the 'severity' from a feature request to 'Serious', as locking improvements now
have the top priority.

Reassigning this to Jan Lindstrom.

Regards,

Heikki

A further note: maybe best to put the changes to 5.1 from now on. Breaking locking in 5.0
would not be nice.

Regards,
Heikki

I am reassigning this bug to Marko Mäkelä. We will work together with Marko so that the
bug gets resolved as soon as possible.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30336

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30816

Patch to unlock_row() has been send to review. This patch should fix a earlier problem
that unlock_row unlocked all locks to row not only lock this transaction earlier
requested for this row.

Regards,
    Jan

Both patches (the one by me and another by Jan Lindström) have been approved by Heikki
and me. They should be applied to the MySQL 5.0 tree shortly.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32876

Documented bugfix in 5.1.5 changelog.

Need specific 5.0.x version number to which the patch applies in that tree for completing
documentation of this issue. Thanks.

Jon,

since the patch is mostly useful only with RBR, I doubt that the patch will ever be
pushed to 5.0.

Regards,

Heikki

Hi Jon,

Heikki is right, this fix only went into 5.1.

cheers,
-elliot

Closing since jon reported the fix documented in 5.1.

The fix of this bug introduced Bug #31494.