Bug #11833 Cursor doesn't use snapshot of data after mysql_stmt_execute
Submitted: 9 Jul 2005 11:29 Modified: 12 Jul 2005 17:13
Reporter: Georg Richter Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.10-beta (cs: 1.901) OS:Linux (Linux)
Assigned to: Konstantin Osipov CPU Architecture:Any

[9 Jul 2005 11:29] Georg Richter
Description:
When a second connection performs a transaction (e.g. DELETE on same table) cursor doesn't use the snapshot from point in time of mysql_stmt_execute.

See the sample test program below. When moving the delete statement behind the first fetch statement the data are consistent.

How to repeat:
/* 
   bug_0708_1.c
   open cursor should use a current snapshot of data after mysql_stmt_execute,
   not after 1st mysql_stmt_fetch.
*/

#include <stdio.h>
#include <mysql.h>

MYSQL_STMT *open_cursor(MYSQL *mysql, char *query)
{
  ulong ctype = CURSOR_TYPE_READ_ONLY;
  MYSQL_STMT *stmt = mysql_stmt_init(mysql);

  mysql_stmt_prepare(stmt, query, strlen(query));
  mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, &ctype);

  return stmt;
}

int main() {
  MYSQL *mysql[2];   
  MYSQL_STMT *stmt;
  int cnt = 0;
   
  mysql[0] = mysql_init(NULL);
  mysql[1] = mysql_init(NULL);
  mysql_real_connect(mysql[0], "localhost", "root", "", "test", 0, NULL, 0);
  mysql_real_connect(mysql[1], "localhost", "root", "", "test", 0, NULL, 0);

  mysql_query(mysql[1], "DROP TABLE IF EXISTS t1");
  mysql_query(mysql[1], "CREATE TABLE t1 (a int, index(a)) engine=InnoDB");
  mysql_query(mysql[1], "INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7)");

  stmt = open_cursor(mysql[0], "SELECT a FROM t1 ORDER BY a");
  mysql_stmt_execute(stmt);

  /* if we move this query behind first mysql_stmt_fetch, all (7) rows are fetched */
  mysql_query(mysql[1], "DELETE FROM t1 WHERE a < 4");

  if (!mysql_stmt_fetch(stmt))
    cnt++;

  while (!mysql_stmt_fetch(stmt))
    cnt++;

  printf("%d Rows fetched from cursor\n", cnt);
  mysql_stmt_close(stmt);
  mysql_close(mysql[0]);
  mysql_close(mysql[1]);
}
[9 Jul 2005 14:05] MySQL Verification Team
miguel@hegel:/share/dbs/5.0/bin$ ./bug11833
4 Rows fetched from cursor
[11 Jul 2005 11:20] Heikki Tuuri
Georg,

is there some compelling reason why it SHOULD use the snapshot from the first ...execute()?

What happens is that InnoDB assigns the snapshot at the first fetch. On line 3457 of row0sel.c:

        if (!prebuilt->sql_stat_start) {
                /* No need to set an intention lock or assign a read view */

                if (trx->read_view == NULL
                    && prebuilt->select_lock_type == LOCK_NONE) {

                        fputs(
"InnoDB: Error: MySQL is trying to perform a consistent read\n"
"InnoDB: but the read view is not assigned!\n", stderr);
                        trx_print(stderr, trx);
                        fputc('\n', stderr);
                        ut_a(0);
                }
        } else if (prebuilt->select_lock_type == LOCK_NONE) {
                /* This is a consistent read */
                /* Assign a read view for the query */

                trx_assign_read_view(trx);
                prebuilt->sql_stat_start = FALSE;
        } else {

We could, of course, call trx_assign_read_view() earlier. The problem for InnoDB is to know if MySQL is going to call row_search_for_mysql() with prebuilt->select_lock_type == LOCK_NONE. Hmm... SELECT, and various subqueries when innodb_locks_unsafe_for_binlog is set, will use a consistent read.

Regards,

Heikki
[12 Jul 2005 17:13] Georg Richter
Heikki, Konstantin,

According to PeterG there is no definition at which point in time a snapshot has to be used. SQL Server for example also does it during fetch, not during execute.

Changed status to "Not a bug"
[17 Jan 2014 10:58] Marko Mäkelä
For what it is worth, there is
START TRANSACTION WITH CONSISTENT SNAPSHOT
that will create the read view at the time the transaction is started, not at the time of the first fetch of a record.