bk commit into 5.0 tree (heikki:1.1995) BUG#3300

[Heikki Tuuri]

Below is the list of changes that have just been committed into a local
5.0 repository of heikki. When heikki does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1995 05/09/26 19:23:20 heikki@stripped +5 -0
  ha_innodb.cc, handler.h, sql_update.cc, row0mysql.c, row0mysql.h:
    Partial fix to Bug #3300 : make an MySQL UPDATE to understand an 'optimistic' read of
a locked row; the InnoDB code is still missing in this patch

  sql/ha_innodb.cc
    1.262 05/09/26 19:22:55 heikki@stripped +31 -4
    Partial fix to Bug #3300 : make an MySQL UPDATE to understand an 'optimistic' read of
a locked row; the InnoDB code is still missing in this patch

  sql/handler.h
    1.155 05/09/26 19:22:43 heikki@stripped +16 -0
    Partial fix to Bug #3300 : make an MySQL UPDATE to understand an 'optimistic' read of
a locked row; the InnoDB code is still missing in this patch

  sql/sql_update.cc
    1.172 05/09/26 19:22:37 heikki@stripped +17 -0
    Partial fix to Bug #3300 : make an MySQL UPDATE to understand an 'optimistic' read of
a locked row; the InnoDB code is still missing in this patch

  innobase/row/row0mysql.c
    1.115 05/09/26 19:22:17 heikki@stripped +3 -0
    Partial fix to Bug #3300 : make an MySQL UPDATE to understand an 'optimistic' read of
a locked row; the InnoDB code is still missing in this patch

  innobase/include/row0mysql.h
    1.46 05/09/26 19:22:11 heikki@stripped +27 -0
    Partial fix to Bug #3300 : make an MySQL UPDATE to understand an 'optimistic' read of
a locked row; the InnoDB code is still missing in this patch

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	heikki
# Host:	hundin.mysql.fi
# Root:	/home/heikki/mysql-5.0

--- 1.154/sql/handler.h	2005-09-23 02:58:04 +03:00
+++ 1.155/sql/handler.h	2005-09-26 19:22:43 +03:00
@@ -646,6 +646,22 @@
   { return extra(operation); }
   virtual int reset() { return extra(HA_EXTRA_RESET); }
   virtual int external_lock(THD *thd, int lock_type) { return 0; }
+  /*
+    In an UPDATE or DELETE, if the row under the cursor was locked by another
+    transaction, and the engine used an optimistic read of the last
+    committed row value under the cursor, then the engine returns 1 from this
+    function. MySQL must NOT try to update this optimistic value. If the
+    optimistic value does not match the WHERE condition, MySQL can decide to
+    skip over this row. Currently only works for InnoDB. This can be used to
+    avoid unnecessary lock waits.
+  */
+  virtual bool update_row_was_read_optimistic() { return 0; }
+  /*
+    If an optimistic read returned a row which matches the WHERE condition
+    in an UPDATE or DELETE, then we uset the following call to indicate that
+    we want to do a normal locking read of the row. Only works in InnoDB.
+  */
+  virtual void force_pessimistic_re_read_of_update_row() {}
   virtual void unlock_row() {}
   virtual int start_stmt(THD *thd) {return 0;}
   /*

--- 1.171/sql/sql_update.cc	2005-09-20 18:41:42 +03:00
+++ 1.172/sql/sql_update.cc	2005-09-26 19:22:37 +03:00
@@ -332,6 +332,15 @@
       {
 	if (!(select && select->skip_record()))
 	{
+          if (table->file->update_row_was_read_optimistic())
+          {
+	    /* we cannot update an optimistically read row */
+
+            table->file->force_pessimistic_re_read_of_update_row();
+
+	    continue;  /* repeat the read of the same row if it still exists */
+          }
+
 	  table->file->position(table->record[0]);
 	  if (my_b_write(&tempfile,table->file->ref,
 			 table->file->ref_length))
@@ -403,6 +412,14 @@
   {
     if (!(select && select->skip_record()))
     {
+      if (table->file->update_row_was_read_optimistic()) {
+	/* we cannot update an optimistically read row */
+
+        table->file->force_pessimistic_re_read_of_update_row();
+
+	continue;  /* repeat the read of the same row if it still exists */
+      }
+
       store_record(table,record[1]);
       if (fill_record_n_invoke_before_triggers(thd, fields, values, 0,
                                                table->triggers,

--- 1.45/innobase/include/row0mysql.h	2005-09-23 11:20:22 +03:00
+++ 1.46/innobase/include/row0mysql.h	2005-09-26 19:22:11 +03:00
@@ -612,6 +612,33 @@
 					that was decided in ha_innodb.cc,
 					::store_lock(), ::external_lock(),
 					etc. */
+	ibool		update_row_was_read_optimistic; /* if
+					innodb_locks_unsafe_for_binlog is TRUE,
+					then this is set to TRUE if the row
+					under an UPDATE or DELETE cursor was
+					locked by another transaction, and we
+					resorted to reading the last committed
+					value ('semi-consistent read'); if
+					innodb_locks_unsafe_for_binlog is TRUE
+					and the last committed value of the
+					row does not match the WHERE condition,
+					then MySQL can simply skip over the
+					row; this eliminates lock waits in some
+					cases; note that this breaks
+					serializability */
+	ibool		force_pessimistic_re_read_of_update_row; /* if
+					innodb_locks_unsafe_for_binlog is TRUE,
+					and MySQL finds out that a row read
+					in the semi-consistent way WOULD match
+					the WHERE condition, then MySQL sets
+					this flag TRUE before calling fetch:
+					InnoDB tries to read the same row
+					again, this time using a normal locking
+					read; if purge had removed the row
+					under the cursor, then this flag has
+					no effect and we reset this flag;
+					we reset this flag always when we
+					return a row to MySQL */
 	ulint		mysql_prefix_len;/* byte offset of the end of
 					the last requested column */
 	ulint		mysql_row_len;	/* length in bytes of a row in the

--- 1.114/innobase/row/row0mysql.c	2005-09-23 11:20:22 +03:00
+++ 1.115/innobase/row/row0mysql.c	2005-09-26 19:22:17 +03:00
@@ -626,6 +626,9 @@
 	prebuilt->select_lock_type = LOCK_NONE;
 	prebuilt->stored_select_lock_type = 99999999;
 
+	prebuilt->update_row_was_read_optimistic = FALSE;
+	prebuilt->force_pessimistic_re_read_of_update_row = FALSE;
+
 	prebuilt->sel_graph = NULL;
 
 	prebuilt->search_tuple = dtuple_create(heap,

--- 1.261/sql/ha_innodb.cc	2005-09-23 16:40:54 +03:00
+++ 1.262/sql/ha_innodb.cc	2005-09-26 19:22:55 +03:00
@@ -3672,9 +3672,9 @@
 }
 
 /**************************************************************************
-Removes a new lock set on a row. This can be called after a row has been read
-in the processing of an UPDATE or a DELETE query, if the option
-innodb_locks_unsafe_for_binlog is set. */
+Removes a new lock set on a row, if it was not read optimistically. This can 
+be called after a row has been read in the processing of an UPDATE or a DELETE
+query, if the option innodb_locks_unsafe_for_binlog is set. */
 
 void
 ha_innobase::unlock_row(void)
@@ -3694,7 +3694,34 @@
 	}
 	
 	if (srv_locks_unsafe_for_binlog) {
-		row_unlock_for_mysql(prebuilt, FALSE);
+		if (!prebuilt->update_row_was_read_optimistic) {
+
+			row_unlock_for_mysql(prebuilt, FALSE);
+		}
+		prebuilt->update_row_was_read_optimistic = FALSE;
+		prebuilt->force_pessimistic_re_read_of_update_row = FALSE;
+	}
+}
+
+/* See handler.h and row0mysql.h for docs on this function. */
+bool
+ha_innobase::update_row_was_read_optimistic(void)
+/*=============================================*/
+{
+	row_prebuilt_t*	prebuilt = (row_prebuilt_t*) innobase_prebuilt;
+
+	return((bool)prebuilt->update_row_was_read_optimistic);
+}
+
+/* See handler.h and row0mysql.h for docs on this function. */
+void
+ha_innobase::force_pessimistic_re_read_of_update_row(void)
+/*======================================================*/
+{
+	row_prebuilt_t*	prebuilt = (row_prebuilt_t*) innobase_prebuilt;
+
+	if (srv_locks_unsafe_for_binlog) {
+		prebuilt->force_pessimistic_re_read_of_update_row = TRUE;
 	}
 }