From: Date: November 30 2005 8:21am Subject: bk commit into 5.1 tree (elliot:1.1975) BUG#3300 List-Archive: http://lists.mysql.com/internals/32876 X-Bug: 3300 Message-Id: <20051130072158.A5BB2204228@localhost.localdomain> Below is the list of changes that have just been committed into a local 5.1 repository of emurphy. When emurphy 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.1975 05/11/30 02:21:43 elliot@stripped +15 -0 This changeset is a rollup of several patches from InnoDB for BUG#3300 plus test cases from Sinisa. This fix adds semi-consistent read capability and extends the handler interface. Pushing into RBR clone at first since this will be delivered to customer. mysql-test/t/innodb_concurrent.test 1.1 05/11/30 02:21:37 elliot@stripped +342 -0 Test case from Sinisa for InnoDB semi-consistent reads (BUG#3300) mysql-test/t/innodb_concurrent-master.opt 1.1 05/11/30 02:21:37 elliot@stripped +1 -0 Test case from Sinisa for InnoDB semi-consistent reads (BUG#3300) mysql-test/t/innodb_concurrent.test 1.0 05/11/30 02:21:37 elliot@stripped +0 -0 BitKeeper file /home/emurphy/src/mysql/work/mysql-5.1-wl1012-v3-bug3300/mysql-test/t/innodb_concurrent.test mysql-test/t/innodb_concurrent-master.opt 1.0 05/11/30 02:21:37 elliot@stripped +0 -0 BitKeeper file /home/emurphy/src/mysql/work/mysql-5.1-wl1012-v3-bug3300/mysql-test/t/innodb_concurrent-master.opt mysql-test/r/innodb_concurrent.result 1.1 05/11/30 02:21:36 elliot@stripped +621 -0 Test case from Sinisa for InnoDB semi-consistent reads (BUG#3300) storage/innobase/row/row0vers.c 1.18 05/11/30 02:21:36 elliot@stripped +138 -0 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads storage/innobase/row/row0sel.c 1.99 05/11/30 02:21:36 elliot@stripped +159 -13 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads storage/innobase/row/row0mysql.c 1.113 05/11/30 02:21:36 elliot@stripped +4 -10 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads storage/innobase/lock/lock0lock.c 1.63 05/11/30 02:21:36 elliot@stripped +67 -1 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads mysql-test/r/innodb_concurrent.result 1.0 05/11/30 02:21:36 elliot@stripped +0 -0 BitKeeper file /home/emurphy/src/mysql/work/mysql-5.1-wl1012-v3-bug3300/mysql-test/r/innodb_concurrent.result storage/innobase/include/row0vers.h 1.5 05/11/30 02:21:35 elliot@stripped +26 -0 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads storage/innobase/include/row0mysql.h 1.45 05/11/30 02:21:35 elliot@stripped +30 -0 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads storage/innobase/include/lock0lock.h 1.25 05/11/30 02:21:34 elliot@stripped +12 -8 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads sql/sql_update.cc 1.177 05/11/30 02:21:34 elliot@stripped +13 -0 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads sql/handler.h 1.169 05/11/30 02:21:34 elliot@stripped +20 -0 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads sql/ha_innodb.h 1.109 05/11/30 02:21:34 elliot@stripped +2 -0 Applying patch from InnoDB for BUG#3300, enabling semi-consistent reads sql/ha_innodb.cc 1.239 05/11/30 02:21:34 elliot@stripped +57 -7 Applying patch from InnoDB for bug#3300, enabling semi-consistent reads Also contains a change to disable semi-consistent read in rnd_init when scan is false, as requested by Sergei during review. mysql-test/t/disabled.def 1.15 05/11/30 02:21:34 elliot@stripped +1 -0 Disabling innodb_concurrent for now as the results are not deterministic # 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: elliot # Host: xan.(none) # Root: /home/emurphy/src/mysql/work/mysql-5.1-wl1012-v3-bug3300 --- 1.168/sql/handler.h 2005-11-21 05:07:03 -05:00 +++ 1.169/sql/handler.h 2005-11-30 02:21:34 -05:00 @@ -1179,6 +1179,26 @@ virtual int extra_opt(enum ha_extra_function operation, ulong cache_size) { return extra(operation); } 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. + + If this method returns nonzero, it will also signal the storage + engine that the next read will be a locking re-read of the row. + */ + virtual bool was_semi_consistent_read() { return 0; } + /* + Tell the engine whether it should avoid unnecessary lock waits. + If yes, in an UPDATE or DELETE, if the row under the cursor was locked + by another transaction, the engine may try an optimistic read of + the last committed row value under the cursor. + */ + virtual void try_semi_consistent_read(bool) {} virtual void unlock_row() {} virtual int start_stmt(THD *thd, thr_lock_type lock_type) {return 0;} /* --- 1.176/sql/sql_update.cc 2005-11-19 10:33:23 -05:00 +++ 1.177/sql/sql_update.cc 2005-11-30 02:21:34 -05:00 @@ -355,6 +355,9 @@ /* If quick select is used, initialize it before retrieving rows. */ if (select && select->quick && select->quick->reset()) goto err; + + table->file->try_semi_consistent_read(1); + if (used_index == MAX_KEY || (select && select->quick)) init_read_record(&info,thd,table,select,0,1); else @@ -367,6 +370,9 @@ { if (!(select && select->skip_record())) { + if (table->file->was_semi_consistent_read()) + 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)) @@ -386,6 +392,7 @@ if (thd->killed && !error) error= 1; // Aborted limit= tmp_limit; + table->file->try_semi_consistent_read(0); end_read_record(&info); /* Change select to use tempfile */ @@ -420,6 +427,7 @@ if (select && select->quick && select->quick->reset()) goto err; + table->file->try_semi_consistent_read(1); init_read_record(&info,thd,table,select,0,1); updated= found= 0; @@ -435,10 +443,14 @@ (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES))); will_batch= !table->file->start_bulk_update(); + while (!(error=info.read_record(&info)) && !thd->killed) { if (!(select && select->skip_record())) { + if (table->file->was_semi_consistent_read()) + 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, @@ -589,6 +601,7 @@ updated-= dup_key_found; if (will_batch) table->file->end_bulk_update(); + table->file->try_semi_consistent_read(0); end_read_record(&info); free_io_cache(table); // If ORDER BY delete select; --- New file --- +++ mysql-test/r/innodb_concurrent.result 05/11/30 02:21:36 drop table if exists t1; Warnings: Note 1051 Unknown table 't1' create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",1); get_lock("hello",1) 1 begin; update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; begin; update t1 set eta=2 where tipo=22; select release_lock("hello"); release_lock("hello") 1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 0 begin; update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; begin; update t1 set tipo=1 where tipo=2; select release_lock("hello"); release_lock("hello") 0 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 1 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 1 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 1 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 1 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 1 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 1 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 1 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 1 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 1 1 ccccccccccccccccccccccccccccccccccccccccccc 20 1 ddddddddddddddddddddddddddddddddddddddddddd 1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 1 fffffffffffffffffffffffffffffffffffffffffff 1 1 ggggggggggggggggggggggggggggggggggggggggggg 60 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 1 1 ccccccccccccccccccccccccccccccccccccccccccc 20 1 ddddddddddddddddddddddddddddddddddddddddddd 1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 1 fffffffffffffffffffffffffffffffffffffffffff 1 1 ggggggggggggggggggggggggggggggggggggggggggg 60 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; create table t1 (a int not null, b int not null) engine=innodb; insert into t1 values (1,1),(2,1),(3,1),(4,1); select get_lock("hello2",1000); get_lock("hello2",1000) 1 begin; update t1 set b=10+get_lock(concat("hello",a),1000)*0 where a=2; insert into t1 values (1,1); select release_lock("hello2"); release_lock("hello2") 1 select * from t1; a b 1 1 2 1 3 1 4 1 1 1 select * from t1; a b 1 1 2 10 3 1 4 1 1 1 commit; drop table t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 1 begin; select * from t1 where tipo=2 FOR UPDATE; begin; select release_lock("hello"); release_lock("hello") 1 update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 1 begin; select * from t1 where tipo=2 FOR UPDATE; begin; select release_lock("hello"); release_lock("hello") 1 update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 1 begin; select * from t1 where tipo=2 FOR UPDATE; begin; select release_lock("hello"); release_lock("hello") 1 select * from t1 where tipo=1 FOR UPDATE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 1 begin; delete from t1 where tipo=2; begin; select release_lock("hello"); release_lock("hello") 1 update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 50 1 ggggggggggggggggggggggggggggggggggggggggggg 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 50 1 ggggggggggggggggggggggggggggggggggggggggggg 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 50 1 ggggggggggggggggggggggggggggggggggggggggggg 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); get_lock("hello",10) 1 begin; delete from t1 where tipo=2; begin; select release_lock("hello"); release_lock("hello") 1 update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 20 2 ddddddddddddddddddddddddddddddddddddddddddd 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 2 fffffffffffffffffffffffffffffffffffffffffff 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 50 1 ggggggggggggggggggggggggggggggggggggggggggg 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; commit; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 50 1 ggggggggggggggggggggggggggggggggggggggggggg 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 10 1 ccccccccccccccccccccccccccccccccccccccccccc 30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 50 1 ggggggggggggggggggggggggggggggggggggggggggg 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk drop table t1; --- New file --- +++ mysql-test/t/innodb_concurrent-master.opt 05/11/30 02:21:37 --innodb_locks_unsafe_for_binlog --- New file --- +++ mysql-test/t/innodb_concurrent.test 05/11/30 02:21:37 # # Concurrent InnoDB tests, mainly in UPDATE's # Bug#3300 # Designed and tested by Sinisa Milivojevic, sinisa@stripped # # two non-interfering UPDATE's not changing result set # connection default; drop table if exists t1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; connect (thread1, localhost, mysqltest,,); connection thread1; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",1); connect (thread2, localhost, mysqltest,,); connection thread2; begin; send update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; sleep 1; connection thread1; begin; update t1 set eta=2 where tipo=22; select release_lock("hello"); select * from t1; connection thread2; reap; select * from t1; send commit; connection thread1; select * from t1; commit; select * from t1; connection thread2; reap; select * from t1; connection thread1; select * from t1; connection default; drop table t1; # # two UPDATE's running and one changing result set # #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); #connect (thread2, localhost, mysqltest,,); connection thread2; begin; send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; sleep 1; connection thread1; begin; update t1 set tipo=1 where tipo=2; select release_lock("hello"); select * from t1; connection thread2; reap; select * from t1; send commit; connection thread1; select * from t1; commit; select * from t1; connection thread2; reap; select * from t1; connection thread1; select * from t1; connection default; drop table t1; # # One UPDATE and one INSERT .... Monty's test # #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1 (a int not null, b int not null) engine=innodb; insert into t1 values (1,1),(2,1),(3,1),(4,1); select get_lock("hello2",1000); #connect (thread2, localhost, mysqltest,,); connection thread2; begin; send update t1 set b=10+get_lock(concat("hello",a),1000)*0 where a=2; sleep 1; connection thread1; insert into t1 values (1,1); select release_lock("hello2"); select * from t1; connection thread2; reap; select * from t1; send commit; connection thread1; sleep 1; connection thread2; reap; connection default; drop table t1; # # one UPDATE changing result set and SELECT ... FOR UPDATE # #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); #connect (thread2, localhost, mysqltest,,); connection thread2; begin; send select * from t1 where tipo=2 FOR UPDATE; sleep 1; connection thread1; begin; select release_lock("hello"); --error 1205 update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; select * from t1; connection thread2; reap; select * from t1; send commit; connection thread1; commit; connection thread2; reap; select * from t1; connection thread1; select * from t1; connection default; drop table t1; # # one UPDATE not changing result set and SELECT ... FOR UPDATE # #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); #connect (thread2, localhost, mysqltest,,); connection thread2; begin; send select * from t1 where tipo=2 FOR UPDATE; sleep 1; connection thread1; begin; select release_lock("hello"); --error 1205 update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; select * from t1; connection thread2; reap; select * from t1; send commit; connection thread1; commit; connection thread2; reap; select * from t1; connection thread1; select * from t1; connection default; drop table t1; # # two SELECT ... FOR UPDATE # #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); #connect (thread2, localhost, mysqltest,,); connection thread2; begin; send select * from t1 where tipo=2 FOR UPDATE; sleep 1; connection thread1; begin; select release_lock("hello"); --error 1205 select * from t1 where tipo=1 FOR UPDATE; connection thread2; reap; select * from t1; send commit; connection thread1; commit; connection thread2; reap; select * from t1; connection thread1; select * from t1; connection default; drop table t1; # # one UPDATE changing result set and DELETE # #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); #connect (thread2, localhost, mysqltest,,); connection thread2; begin; send delete from t1 where tipo=2; sleep 1; connection thread1; begin; select release_lock("hello"); --error 1205 update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; select * from t1; connection thread2; reap; select * from t1; send commit; connection thread1; commit; connection thread2; reap; select * from t1; connection thread1; select * from t1; connection default; drop table t1; # # one UPDATE not changing result set and DELETE # #connect (thread1, localhost, mysqltest,,); connection thread1; create table t1(eta int(11) not null, tipo int(11), c varchar(255)) type=innodb; insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); select get_lock("hello",10); #connect (thread2, localhost, mysqltest,,); connection thread2; begin; send delete from t1 where tipo=2; sleep 1; connection thread1; begin; select release_lock("hello"); update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; select * from t1; connection thread2; reap; select * from t1; send commit; connection thread1; commit; connection thread2; reap; select * from t1; connection thread1; select * from t1; connection default; sleep 1; drop table t1; disconnect thread1; disconnect thread2; --- 1.14/mysql-test/t/disabled.def 2005-11-21 10:36:37 -05:00 +++ 1.15/mysql-test/t/disabled.def 2005-11-30 02:21:34 -05:00 @@ -23,3 +23,4 @@ rpl_row_NOW:Bug 12574 rpl_bit_npk:Bug 13418 compress : Magnus will fix +innodb_concurrent: Results are not deterministic --- 1.24/storage/innobase/include/lock0lock.h 2005-06-21 02:49:16 -04:00 +++ 1.25/storage/innobase/include/lock0lock.h 2005-11-30 02:21:34 -05:00 @@ -64,14 +64,6 @@ dict_index_t* index, /* in: clustered index */ const ulint* offsets);/* in: rec_get_offsets(rec, index) */ /***************************************************************** -Resets the lock bits for a single record. Releases transactions -waiting for lock requests here. */ - -void -lock_rec_reset_and_release_wait( -/*============================*/ - rec_t* rec); /* in: record whose locks bits should be reset */ -/***************************************************************** Makes a record to inherit the locks of another record as gap type locks, but does not reset the lock bits of the other record. Also waiting lock requests on rec are inherited as GRANTED gap locks. */ @@ -427,6 +419,18 @@ /*=============*/ /* out: TRUE if there are lock(s) */ dict_table_t* table); /* in: database table in dictionary cache */ +/***************************************************************** +Removes a granted record lock of a transaction from the queue and grants +locks to other transactions waiting in the queue if they now are entitled +to a lock. */ + +void +lock_rec_unlock( +/*============*/ + trx_t* trx, /* in: transaction that has set a record + lock */ + rec_t* rec, /* in: record */ + ulint lock_mode); /* in: LOCK_S or LOCK_X */ /************************************************************************* Releases a table lock. Releases possible other transactions waiting for this lock. */ --- 1.44/storage/innobase/include/row0mysql.h 2005-10-06 04:26:04 -04:00 +++ 1.45/storage/innobase/include/row0mysql.h 2005-11-30 02:21:35 -05:00 @@ -612,6 +612,31 @@ that was decided in ha_innodb.cc, ::store_lock(), ::external_lock(), etc. */ + ulint row_read_type; /* ROW_READ_WITH_LOCKS if row locks + should be the obtained for records + under an UPDATE or DELETE cursor. + If innodb_locks_unsafe_for_binlog + is TRUE, this can be set to + ROW_READ_TRY_SEMI_CONSISTENT, so that + if the row under an UPDATE or DELETE + cursor was locked by another + transaction, InnoDB will resort + to reading the last committed value + ('semi-consistent read'). Then, + this field will be set to + ROW_READ_DID_SEMI_CONSISTENT to + indicate that. If the row does not + match the WHERE condition, MySQL will + invoke handler::unlock_row() to + clear the flag back to + ROW_READ_TRY_SEMI_CONSISTENT and + to simply skip the row. If + the row matches, the next call to + row_search_for_mysql() will lock + the row. + This eliminates lock waits in some + cases; note that this breaks + serializability. */ 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 @@ -656,6 +681,11 @@ /* Values for hint_need_to_fetch_extra_cols */ #define ROW_RETRIEVE_PRIMARY_KEY 1 #define ROW_RETRIEVE_ALL_COLS 2 + +/* Values for row_read_type */ +#define ROW_READ_WITH_LOCKS 0 +#define ROW_READ_TRY_SEMI_CONSISTENT 1 +#define ROW_READ_DID_SEMI_CONSISTENT 2 #ifndef UNIV_NONINL --- 1.4/storage/innobase/include/row0vers.h 2005-04-07 19:37:33 -04:00 +++ 1.5/storage/innobase/include/row0vers.h 2005-11-30 02:21:35 -05:00 @@ -92,6 +92,32 @@ record does not exist in the view, that is, it was freshly inserted afterwards */ +/********************************************************************* +Constructs the last committed version of a clustered index record, +which should be seen by a semi-consistent read. */ + +ulint +row_vers_build_for_semi_consistent_read( +/*====================================*/ + /* out: DB_SUCCESS or DB_MISSING_HISTORY */ + rec_t* rec, /* in: record in a clustered index; the + caller must have a latch on the page; this + latch locks the top of the stack of versions + of this records */ + mtr_t* mtr, /* in: mtr holding the latch on rec */ + dict_index_t* index, /* in: the clustered index */ + ulint** offsets,/* in/out: offsets returned by + rec_get_offsets(rec, index) */ + mem_heap_t** offset_heap,/* in/out: memory heap from which + the offsets are allocated */ + mem_heap_t* in_heap,/* in: memory heap from which the memory for + old_vers is allocated; memory for possible + intermediate versions is allocated and freed + locally within the function */ + rec_t** old_vers);/* out, own: rec, old version, or NULL if the + record does not exist in the view, that is, + it was freshly inserted afterwards */ + #ifndef UNIV_NONINL #include "row0vers.ic" --- 1.62/storage/innobase/lock/lock0lock.c 2005-09-02 19:06:55 -04:00 +++ 1.63/storage/innobase/lock/lock0lock.c 2005-11-30 02:21:36 -05:00 @@ -2392,7 +2392,7 @@ /***************************************************************** Resets the lock bits for a single record. Releases transactions waiting for lock requests here. */ - +static void lock_rec_reset_and_release_wait( /*============================*/ @@ -3759,6 +3759,72 @@ } /*=========================== LOCK RELEASE ==============================*/ + +/***************************************************************** +Removes a granted record lock of a transaction from the queue and grants +locks to other transactions waiting in the queue if they now are entitled +to a lock. */ + +void +lock_rec_unlock( +/*============*/ + trx_t* trx, /* in: transaction that has set a record + lock */ + rec_t* rec, /* in: record */ + ulint lock_mode) /* in: LOCK_S or LOCK_X */ +{ + lock_t* lock; + ulint heap_no; + + ut_ad(trx && rec); + + mutex_enter(&kernel_mutex); + + heap_no = rec_get_heap_no(rec, page_rec_is_comp(rec)); + + lock = lock_rec_get_first(rec); + + /* Remove the record lock */ + + while (lock != NULL) { + if (lock->trx == trx && lock_get_mode(lock) == lock_mode) { + ut_a(!lock_get_wait(lock)); + + lock_rec_reset_nth_bit(lock, heap_no); + + break; + } + + lock = lock_rec_get_next(rec, lock); + } + + if (UNIV_UNLIKELY(lock == NULL)) { + mutex_exit(&kernel_mutex); + ut_print_timestamp(stderr); + fprintf(stderr, +" InnoDB: Error: unlock row could not find a %lu mode lock on the record\n", + (ulong)lock_mode); + + return; + } + + /* Check if we can now grant waiting lock requests */ + + lock = lock_rec_get_first(rec); + + while (lock != NULL) { + if (lock_get_wait(lock) + && !lock_rec_has_to_wait_in_queue(lock)) { + + /* Grant the lock */ + lock_grant(lock); + } + + lock = lock_rec_get_next(rec, lock); + } + + mutex_exit(&kernel_mutex); +} /************************************************************************* Releases a table lock. --- 1.112/storage/innobase/row/row0mysql.c 2005-10-06 04:26:04 -04:00 +++ 1.113/storage/innobase/row/row0mysql.c 2005-11-30 02:21:36 -05:00 @@ -626,6 +626,8 @@ prebuilt->select_lock_type = LOCK_NONE; prebuilt->stored_select_lock_type = 99999999; + prebuilt->row_read_type = ROW_READ_WITH_LOCKS; + prebuilt->sel_graph = NULL; prebuilt->search_tuple = dtuple_create(heap, @@ -1486,11 +1488,7 @@ rec = btr_pcur_get_rec(pcur); - mutex_enter(&kernel_mutex); - - lock_rec_reset_and_release_wait(rec); - - mutex_exit(&kernel_mutex); + lock_rec_unlock(trx, rec, prebuilt->select_lock_type); mtr_commit(&mtr); @@ -1520,11 +1518,7 @@ rec = btr_pcur_get_rec(clust_pcur); - mutex_enter(&kernel_mutex); - - lock_rec_reset_and_release_wait(rec); - - mutex_exit(&kernel_mutex); + lock_rec_unlock(trx, rec, prebuilt->select_lock_type); mtr_commit(&mtr); } --- 1.98/storage/innobase/row/row0sel.c 2005-09-06 03:11:52 -04:00 +++ 1.99/storage/innobase/row/row0sel.c 2005-11-30 02:21:36 -05:00 @@ -536,6 +536,41 @@ } /************************************************************************* +Builds the last committed version of a clustered index record for a +semi-consistent read. */ +static +ulint +row_sel_build_committed_vers_for_mysql( +/*===================================*/ + /* out: DB_SUCCESS or error code */ + dict_index_t* clust_index, /* in: clustered index */ + row_prebuilt_t* prebuilt, /* in: prebuilt struct */ + rec_t* rec, /* in: record in a clustered index */ + ulint** offsets, /* in/out: offsets returned by + rec_get_offsets(rec, clust_index) */ + mem_heap_t** offset_heap, /* in/out: memory heap from which + the offsets are allocated */ + rec_t** old_vers, /* out: old version, or NULL if the + record does not exist in the view: + i.e., it was freshly inserted + afterwards */ + mtr_t* mtr) /* in: mtr */ +{ + ulint err; + + if (prebuilt->old_vers_heap) { + mem_heap_empty(prebuilt->old_vers_heap); + } else { + prebuilt->old_vers_heap = mem_heap_create(200); + } + + err = row_vers_build_for_semi_consistent_read(rec, mtr, clust_index, + offsets, offset_heap, + prebuilt->old_vers_heap, old_vers); + return(err); +} + +/************************************************************************* Tests the conditions which determine when the index segment we are searching through has been exhausted. */ UNIV_INLINE @@ -3066,7 +3101,6 @@ rec_t* rec; rec_t* result_rec; rec_t* clust_rec; - rec_t* old_vers; ulint err = DB_SUCCESS; ibool unique_search = FALSE; ibool unique_search_from_clust_index = FALSE; @@ -3077,6 +3111,11 @@ locking SELECT, and the isolation level is <= TRX_ISO_READ_COMMITTED, then this is set to FALSE */ + ibool did_semi_consistent_read = FALSE; + /* if the returned record was locked + and we did a semi-consistent read + (fetch the newest committed version), + then this is set to TRUE */ #ifdef UNIV_SEARCH_DEBUG ulint cnt = 0; #endif /* UNIV_SEARCH_DEBUG */ @@ -3163,7 +3202,7 @@ trx->search_latch_timeout = BTR_SEA_TIMEOUT; } - /* Reset the new record lock info if we srv_locks_unsafe_for_binlog + /* Reset the new record lock info if srv_locks_unsafe_for_binlog is set. Then we are able to remove the record locks set here on an individual row. */ @@ -3431,9 +3470,28 @@ clust_index = dict_table_get_first_index(index->table); if (UNIV_LIKELY(direction != 0)) { - if (!sel_restore_position_for_mysql(&same_user_rec, - BTR_SEARCH_LEAF, - pcur, moves_up, &mtr)) { + ibool need_to_process = sel_restore_position_for_mysql( + &same_user_rec, BTR_SEARCH_LEAF, + pcur, moves_up, &mtr); + + if (UNIV_UNLIKELY(need_to_process)) { + if (UNIV_UNLIKELY(prebuilt->row_read_type + == ROW_READ_DID_SEMI_CONSISTENT)) { + /* We did a semi-consistent read, + but the record was removed in + the meantime. */ + prebuilt->row_read_type + = ROW_READ_TRY_SEMI_CONSISTENT; + } + } else if (UNIV_LIKELY(prebuilt->row_read_type + != ROW_READ_DID_SEMI_CONSISTENT)) { + + /* The cursor was positioned on the record + that we returned previously. If we need + to repeat a semi-consistent read as a + pessimistic locking read, the record + cannot be skipped. */ + goto next_rec; } @@ -3751,7 +3809,64 @@ prebuilt->select_lock_type, lock_type, thr); - if (err != DB_SUCCESS) { + switch (err) { + rec_t* old_vers; + case DB_SUCCESS: + break; + case DB_LOCK_WAIT: + if (UNIV_LIKELY(prebuilt->row_read_type + != ROW_READ_TRY_SEMI_CONSISTENT) + || index != clust_index) { + + goto lock_wait_or_error; + } + + /* The following call returns 'offsets' + associated with 'old_vers' */ + err = row_sel_build_committed_vers_for_mysql( + clust_index, prebuilt, rec, + &offsets, &heap, + &old_vers, &mtr); + + if (err != DB_SUCCESS) { + + goto lock_wait_or_error; + } + + mutex_enter(&kernel_mutex); + if (trx->was_chosen_as_deadlock_victim) { + mutex_exit(&kernel_mutex); + + goto lock_wait_or_error; + } + if (UNIV_LIKELY(trx->wait_lock != NULL)) { + lock_cancel_waiting_and_release( + trx->wait_lock); + trx_reset_new_rec_lock_info(trx); + } else { + mutex_exit(&kernel_mutex); + + /* The lock was granted while we were + searching for the last committed version. + Do a normal locking read. */ + + offsets = rec_get_offsets(rec, index, offsets, + ULINT_UNDEFINED, &heap); + err = DB_SUCCESS; + break; + } + mutex_exit(&kernel_mutex); + + if (old_vers == NULL) { + /* The row was not yet committed */ + + goto next_rec; + } + + did_semi_consistent_read = TRUE; + rec = old_vers; + break; + default: goto lock_wait_or_error; } @@ -3775,6 +3890,7 @@ && !lock_clust_rec_cons_read_sees(rec, index, offsets, trx->read_view)) { + rec_t* old_vers; /* The following call returns 'offsets' associated with 'old_vers' */ err = row_sel_build_prev_vers_for_mysql( @@ -3821,14 +3937,13 @@ /* The record is delete-marked: we can skip it */ if (srv_locks_unsafe_for_binlog - && prebuilt->select_lock_type != LOCK_NONE) { + && prebuilt->select_lock_type != LOCK_NONE + && !did_semi_consistent_read) { /* No need to keep a lock on a delete-marked record if we do not want to use next-key locking. */ row_unlock_for_mysql(prebuilt, TRUE); - - trx_reset_new_rec_lock_info(trx); } goto next_rec; @@ -3882,8 +3997,6 @@ locking. */ row_unlock_for_mysql(prebuilt, TRUE); - - trx_reset_new_rec_lock_info(trx); } goto next_rec; @@ -3977,7 +4090,7 @@ a unique search. */ if (!unique_search_from_clust_index - || prebuilt->select_lock_type == LOCK_X + || prebuilt->select_lock_type != LOCK_NONE || prebuilt->used_in_HANDLER) { /* Inside an update always store the cursor position */ @@ -3990,6 +4103,19 @@ goto normal_return; next_rec: + /* Reset the old and new "did semi-consistent read" flags. */ + if (UNIV_UNLIKELY(prebuilt->row_read_type + == ROW_READ_DID_SEMI_CONSISTENT)) { + prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT; + } + did_semi_consistent_read = FALSE; + + if (UNIV_UNLIKELY(srv_locks_unsafe_for_binlog) + && prebuilt->select_lock_type != LOCK_NONE) { + + trx_reset_new_rec_lock_info(trx); + } + /*-------------------------------------------------------------*/ /* PHASE 5: Move the cursor to the next index record */ @@ -4042,8 +4168,14 @@ goto rec_loop; lock_wait_or_error: - /*-------------------------------------------------------------*/ + /* Reset the old and new "did semi-consistent read" flags. */ + if (UNIV_UNLIKELY(prebuilt->row_read_type + == ROW_READ_DID_SEMI_CONSISTENT)) { + prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT; + } + did_semi_consistent_read = FALSE; + /*-------------------------------------------------------------*/ btr_pcur_store_position(pcur, &mtr); mtr_commit(&mtr); @@ -4125,6 +4257,20 @@ trx->op_info = ""; if (UNIV_LIKELY_NULL(heap)) { mem_heap_free(heap); + } + + /* Set or reset the "did semi-consistent read" flag on return. + The flag did_semi_consistent_read is set if and only if + the record being returned was fetched with a semi-consistent read. */ + ut_ad(prebuilt->row_read_type != ROW_READ_WITH_LOCKS + || !did_semi_consistent_read); + + if (UNIV_UNLIKELY(prebuilt->row_read_type != ROW_READ_WITH_LOCKS)) { + if (UNIV_UNLIKELY(did_semi_consistent_read)) { + prebuilt->row_read_type = ROW_READ_DID_SEMI_CONSISTENT; + } else { + prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT; + } } return(err); } --- 1.17/storage/innobase/row/row0vers.c 2005-06-21 02:49:19 -04:00 +++ 1.18/storage/innobase/row/row0vers.c 2005-11-30 02:21:36 -05:00 @@ -490,3 +490,141 @@ return(err); } + +/********************************************************************* +Constructs the last committed version of a clustered index record, +which should be seen by a semi-consistent read. */ + +ulint +row_vers_build_for_semi_consistent_read( +/*====================================*/ + /* out: DB_SUCCESS or DB_MISSING_HISTORY */ + rec_t* rec, /* in: record in a clustered index; the + caller must have a latch on the page; this + latch locks the top of the stack of versions + of this records */ + mtr_t* mtr, /* in: mtr holding the latch on rec */ + dict_index_t* index, /* in: the clustered index */ + ulint** offsets,/* in/out: offsets returned by + rec_get_offsets(rec, index) */ + mem_heap_t** offset_heap,/* in/out: memory heap from which + the offsets are allocated */ + mem_heap_t* in_heap,/* in: memory heap from which the memory for + old_vers is allocated; memory for possible + intermediate versions is allocated and freed + locally within the function */ + rec_t** old_vers)/* out, own: rec, old version, or NULL if the + record does not exist in the view, that is, + it was freshly inserted afterwards */ +{ + rec_t* version; + mem_heap_t* heap = NULL; + byte* buf; + ulint err; + dulint rec_trx_id; + + ut_ad(index->type & DICT_CLUSTERED); + ut_ad(mtr_memo_contains(mtr, buf_block_align(rec), MTR_MEMO_PAGE_X_FIX) + || mtr_memo_contains(mtr, buf_block_align(rec), + MTR_MEMO_PAGE_S_FIX)); +#ifdef UNIV_SYNC_DEBUG + ut_ad(!rw_lock_own(&(purge_sys->latch), RW_LOCK_SHARED)); +#endif /* UNIV_SYNC_DEBUG */ + + ut_ad(rec_offs_validate(rec, index, *offsets)); + + rw_lock_s_lock(&(purge_sys->latch)); + /* The S-latch on purge_sys prevents the purge view from + changing. Thus, if we have an uncommitted transaction at + this point, then purge cannot remove its undo log even if + the transaction could commit now. */ + + version = rec; + + for (;;) { + trx_t* version_trx; + mem_heap_t* heap2; + rec_t* prev_version; + dulint version_trx_id; + + version_trx_id = row_get_rec_trx_id( + version, index, *offsets); + if (rec == version) { + rec_trx_id = version_trx_id; + } + + mutex_enter(&kernel_mutex); + version_trx = trx_get_on_id(version_trx_id); + mutex_exit(&kernel_mutex); + + if (!version_trx + || version_trx->conc_state == TRX_NOT_STARTED + || version_trx->conc_state == TRX_COMMITTED_IN_MEMORY) { + + /* We found a version that belongs to a + committed transaction: return it. */ + + if (rec == version) { + *old_vers = rec; + err = DB_SUCCESS; + break; + } + + /* We assume that a rolled-back transaction stays in + TRX_ACTIVE state until all the changes have been + rolled back and the transaction is removed from + the global list of transactions. */ + + if (!ut_dulint_cmp(rec_trx_id, version_trx_id)) { + /* The transaction was committed while + we searched for earlier versions. + Return the current version as a + semi-consistent read. */ + + version = rec; + *offsets = rec_get_offsets(version, + index, *offsets, + ULINT_UNDEFINED, offset_heap); + } + + buf = mem_heap_alloc(in_heap, rec_offs_size(*offsets)); + *old_vers = rec_copy(buf, version, *offsets); + rec_offs_make_valid(*old_vers, index, *offsets); + err = DB_SUCCESS; + + break; + } + + heap2 = heap; + heap = mem_heap_create(1024); + + err = trx_undo_prev_version_build(rec, mtr, version, index, + *offsets, heap, &prev_version); + if (heap2) { + mem_heap_free(heap2); /* free version */ + } + + if (UNIV_UNLIKELY(err != DB_SUCCESS)) { + break; + } + + if (prev_version == NULL) { + /* It was a freshly inserted version */ + *old_vers = NULL; + err = DB_SUCCESS; + + break; + } + + version = prev_version; + *offsets = rec_get_offsets(version, index, *offsets, + ULINT_UNDEFINED, offset_heap); + }/* for (;;) */ + + if (heap) { + mem_heap_free(heap); + } + rw_lock_s_unlock(&(purge_sys->latch)); + + return(err); +} --- 1.238/sql/ha_innodb.cc 2005-11-19 10:14:05 -05:00 +++ 1.239/sql/ha_innodb.cc 2005-11-30 02:21:34 -05:00 @@ -3046,6 +3046,9 @@ continue; } + /* In a column prefix index, we may need to truncate + the stored value: */ + cs = key_part->field->charset(); src_start = record + key_part->offset; @@ -3062,7 +3065,11 @@ memcpy(buff, src_start, len); buff+=len; - /* Pad the unused space with spaces */ + /* Pad the unused space with spaces. Note that no + padding is ever needed for UCS-2 because in MySQL, + all UCS2 characters are 2 bytes, as MySQL does not + support surrogate pairs, which are needed to represent + characters in the range U+10000 to U+10FFFF. */ if (len < key_part->length) { len = key_part->length - len; @@ -3785,9 +3792,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) @@ -3797,7 +3804,7 @@ DBUG_ENTER("ha_innobase::unlock_row"); - if (last_query_id != user_thd->query_id) { + if (UNIV_UNLIKELY(last_query_id != user_thd->query_id)) { ut_print_timestamp(stderr); sql_print_error("last_query_id is %lu != user_thd_query_id is " "%lu", (ulong) last_query_id, @@ -3805,9 +3812,45 @@ mem_analyze_corruption((byte *) prebuilt->trx); ut_error; } - - if (srv_locks_unsafe_for_binlog) { + + switch (prebuilt->row_read_type) { + case ROW_READ_WITH_LOCKS: + if (!srv_locks_unsafe_for_binlog) { + break; + } + /* fall through */ + case ROW_READ_TRY_SEMI_CONSISTENT: row_unlock_for_mysql(prebuilt, FALSE); + break; + case ROW_READ_DID_SEMI_CONSISTENT: + prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT; + break; + } + + DBUG_VOID_RETURN; +} + +/* See handler.h and row0mysql.h for docs on this function. */ +bool +ha_innobase::was_semi_consistent_read(void) +/*=======================================*/ +{ + row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt; + + return(prebuilt->row_read_type == ROW_READ_DID_SEMI_CONSISTENT); +} + +/* See handler.h and row0mysql.h for docs on this function. */ +void +ha_innobase::try_semi_consistent_read(bool yes) +/*===========================================*/ +{ + row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt; + + if (yes && srv_locks_unsafe_for_binlog) { + prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT; + } else { + prebuilt->row_read_type = ROW_READ_WITH_LOCKS; } } @@ -4320,6 +4363,13 @@ err = change_active_index(MAX_KEY); } else { err = change_active_index(primary_key); + } + + /* Don't use semi-consistent read in random row reads (by position). + This means we must disable semi_consistent_read if scan is false */ + + if (!scan) { + try_semi_consistent_read(0); } start_of_scan = 1; --- 1.108/sql/ha_innodb.h 2005-11-19 04:41:37 -05:00 +++ 1.109/sql/ha_innodb.h 2005-11-30 02:21:34 -05:00 @@ -122,6 +122,8 @@ int write_row(byte * buf); int update_row(const byte * old_data, byte * new_data); int delete_row(const byte * buf); + bool was_semi_consistent_read(); + void try_semi_consistent_read(bool yes); void unlock_row(); int index_init(uint index, bool sorted);