From 09346e6e2de57fadc94f70beb29eabe5186dbe62 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Dec 2005 06:39:02 +0100 Subject: WL#1012: All changes as one single changeset. This includes both code and test cases. BitKeeper/deleted/.del-ctype_ucs_binlog.result~280d136b1a0bcf17: Delete: mysql-test/r/ctype_ucs_binlog.result BitKeeper/deleted/.del-rpl_delete_all.result~7c050d592614b3f: Delete: mysql-test/r/rpl_delete_all.result BitKeeper/deleted/.del-rpl000013-slave.opt~18266ad8a2403e8d: Delete: mysql-test/t/rpl000013-slave.opt BitKeeper/deleted/.del-rpl_delete_all.test~700a1490277780e0: Delete: mysql-test/t/rpl_delete_all.test mysql-test/extra/binlog_tests/binlog.test: Import patch wl1012.patch mysql-test/extra/binlog_tests/blackhole.test: Import patch wl1012.patch mysql-test/extra/binlog_tests/ctype_cp932.test: Import patch wl1012.patch mysql-test/extra/binlog_tests/ctype_cp932_binlog.test: Import patch wl1012.patch mysql-test/extra/binlog_tests/ctype_ucs_binlog.test: Import patch wl1012.patch mysql-test/extra/binlog_tests/drop_temp_table.test: Import patch wl1012.patch mysql-test/extra/binlog_tests/insert_select-binlog.test: Import patch wl1012.patch mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_ddl.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_deadlock.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_err_ignoredtable.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_flsh_tbls.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_loaddata_m.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_log.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_max_relay_size.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_multi_query.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_reset_slave.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_stm_000001.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_stm_EE_err.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_stm_charset.test: Import patch wl1012.patch mysql-test/extra/rpl_tests/rpl_user_variables.test: Import patch wl1012.patch mysql-test/r/binlog_stm_binlog.result: Import patch wl1012.patch mysql-test/r/binlog_stm_blackhole.result: Import patch wl1012.patch mysql-test/r/binlog_stm_ctype_cp932.result: Import patch wl1012.patch mysql-test/r/binlog_stm_ctype_ucs.result: Import patch wl1012.patch mysql-test/r/binlog_stm_drop_tmp_tbl.result: Import patch wl1012.patch mysql-test/r/binlog_stm_insert_select.result: Import patch wl1012.patch mysql-test/r/binlog_stm_mix_innodb_myisam.result: Import patch wl1012.patch mysql-test/r/rpl_000012.result: Import patch wl1012.patch mysql-test/r/rpl_000015.result: Import patch wl1012.patch mysql-test/r/rpl_deadlock_innodb.result: Import patch wl1012.patch mysql-test/r/rpl_flushlog_loop.result: Import patch wl1012.patch mysql-test/r/rpl_loaddata_s.result: Import patch wl1012.patch mysql-test/r/rpl_stm_000001.result: Import patch wl1012.patch mysql-test/r/rpl_stm_EE_err.result: Import patch wl1012.patch mysql-test/r/rpl_stm_charset.result: Import patch wl1012.patch mysql-test/r/rpl_stm_ddl.result: Import patch wl1012.patch mysql-test/r/rpl_stm_err_ignoredtable.result: Import patch wl1012.patch mysql-test/r/rpl_stm_flsh_tbls.result: Import patch wl1012.patch mysql-test/r/rpl_stm_loaddata_m.result: Import patch wl1012.patch mysql-test/r/rpl_stm_log.result: Import patch wl1012.patch mysql-test/r/rpl_stm_max_relay_size.result: Import patch wl1012.patch mysql-test/r/rpl_stm_multi_query.result: Import patch wl1012.patch mysql-test/r/rpl_stm_mystery22.result: Import patch wl1012.patch mysql-test/r/rpl_stm_reset_slave.result: Import patch wl1012.patch mysql-test/r/rpl_stm_rewrt_db.result: Import patch wl1012.patch mysql-test/r/rpl_stm_sp.result: Import patch wl1012.patch mysql-test/r/rpl_stm_timezone.result: Import patch wl1012.patch mysql-test/r/rpl_stm_until.result: Import patch wl1012.patch mysql-test/r/rpl_stm_user_variables.result: Import patch wl1012.patch mysql-test/r/rpl_stm_view.result: Import patch wl1012.patch mysql-test/t/binlog_row_binlog-master.opt: Import patch wl1012.patch mysql-test/t/rpl_000012.test: Import patch wl1012.patch mysql-test/t/rpl_000015-slave.sh: Import patch wl1012.patch mysql-test/t/rpl_000015.slave-mi: Import patch wl1012.patch mysql-test/t/rpl_000015.test: Import patch wl1012.patch mysql-test/t/rpl_deadlock_innodb-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_flushlog_loop-master.opt: Import patch wl1012.patch mysql-test/t/rpl_flushlog_loop-master.sh: Import patch wl1012.patch mysql-test/t/rpl_flushlog_loop-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_flushlog_loop-slave.sh: Import patch wl1012.patch mysql-test/t/rpl_flushlog_loop.test: Import patch wl1012.patch mysql-test/t/rpl_loaddata_s-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_loaddata_s.test: Import patch wl1012.patch mysql-test/t/rpl_stm_000001-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_err_ignoredtable-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_loaddata_m-master.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_log-master.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_log-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_mystery22.test: Import patch wl1012.patch mysql-test/t/rpl_stm_rewrt_db-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_rewrt_db.test: Import patch wl1012.patch mysql-test/t/rpl_stm_sp-master.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_sp-slave.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_sp.test: Import patch wl1012.patch mysql-test/t/rpl_stm_timezone-master.opt: Import patch wl1012.patch mysql-test/t/rpl_stm_timezone-slave.opt: Import patch wl1012.patch BUILD/SETUP.sh: Import patch wl1012.patch Makefile.am: Import patch wl1012.patch mysql-test/t/rpl_stm_timezone.test: Import patch wl1012.patch mysql-test/t/rpl_stm_until.test: Import patch wl1012.patch mysql-test/t/rpl_stm_view.test: Import patch wl1012.patch client/Makefile.am: Import patch wl1012.patch client/client_priv.h: Import patch wl1012.patch client/mysqlbinlog.cc: Import patch wl1012.patch configure.in: Import patch wl1012.patch include/Makefile.am: Import patch wl1012.patch include/base64.h: Import patch wl1012.patch include/config-win.h: Import patch wl1012.patch include/my_base.h: Import patch wl1012.patch include/my_global.h: Import patch wl1012.patch mysql-test/Makefile.am: Import patch wl1012.patch mysql-test/mysql-test-run.pl: Import patch wl1012.patch mysql-test/mysql-test-run.sh: Import patch wl1012.patch mysql-test/r/date_formats.result: Import patch wl1012.patch mysql-test/r/flush_block_commit.result: Import patch wl1012.patch mysql-test/r/innodb.result: Import patch wl1012.patch mysql-test/r/rpl000017.result: Import patch wl1012.patch mysql-test/r/rpl_change_master.result: Import patch wl1012.patch mysql-test/r/rpl_commit_after_flush.result: Import patch wl1012.patch mysql-test/r/rpl_create_database.result: Import patch wl1012.patch mysql-test/r/rpl_do_grant.result: Import patch wl1012.patch mysql-test/r/rpl_loaddata.result: Import patch wl1012.patch mysql-test/r/rpl_log_pos.result: Import patch wl1012.patch mysql-test/r/rpl_multi_delete.result: Import patch wl1012.patch mysql-test/r/rpl_multi_update.result: Import patch wl1012.patch mysql-test/r/rpl_openssl.result: Import patch wl1012.patch mysql-test/r/rpl_replicate_do.result: Import patch wl1012.patch mysql-test/r/rpl_rotate_logs.result: Import patch wl1012.patch mysql-test/r/rpl_server_id1.result: Import patch wl1012.patch mysql-test/r/rpl_server_id2.result: Import patch wl1012.patch mysql-test/r/rpl_temporary.result: Import patch wl1012.patch mysql-test/r/user_var-binlog.result: Import patch wl1012.patch mysql-test/t/create_select_tmp.test: Import patch wl1012.patch mysql-test/t/date_formats.test: Import patch wl1012.patch mysql-test/t/disabled.def: Import patch wl1012.patch mysql-test/t/innodb.test: Import patch wl1012.patch mysql-test/t/mysqlbinlog.test: Import patch wl1012.patch mysql-test/t/mysqlbinlog2.test: Import patch wl1012.patch mysql-test/t/rpl000002.test: Import patch wl1012.patch mysql-test/t/rpl000006.test: Import patch wl1012.patch mysql-test/t/rpl000013.test: Import patch wl1012.patch mysql-test/t/rpl000017.test: Import patch wl1012.patch mysql-test/t/rpl_auto_increment.test: Import patch wl1012.patch mysql-test/t/rpl_change_master.test: Import patch wl1012.patch mysql-test/t/rpl_commit_after_flush.test: Import patch wl1012.patch mysql-test/t/rpl_create_database.test: Import patch wl1012.patch mysql-test/t/rpl_do_grant.test: Import patch wl1012.patch mysql-test/t/rpl_drop.test: Import patch wl1012.patch mysql-test/t/rpl_empty_master_crash.test: Import patch wl1012.patch mysql-test/t/rpl_failed_optimize.test: Import patch wl1012.patch mysql-test/t/rpl_heap.test: Import patch wl1012.patch mysql-test/t/rpl_insert_id.test: Import patch wl1012.patch mysql-test/t/rpl_insert_ignore.test: Import patch wl1012.patch mysql-test/t/rpl_loaddata.test: Import patch wl1012.patch mysql-test/t/rpl_log_pos.test: Import patch wl1012.patch mysql-test/t/rpl_multi_delete.test: Import patch wl1012.patch mysql-test/t/rpl_multi_update.test: Import patch wl1012.patch mysql-test/t/rpl_multi_update2.test: Import patch wl1012.patch mysql-test/t/rpl_multi_update3.test: Import patch wl1012.patch mysql-test/t/rpl_openssl.test: Import patch wl1012.patch mysql-test/t/rpl_redirect.test: Import patch wl1012.patch mysql-test/t/rpl_relayrotate.test: Import patch wl1012.patch mysql-test/t/rpl_replicate_do.test: Import patch wl1012.patch mysql-test/t/rpl_rotate_logs.test: Import patch wl1012.patch mysql-test/t/rpl_server_id1.test: Import patch wl1012.patch mysql-test/t/rpl_sp_effects.test: Import patch wl1012.patch mysql-test/t/rpl_temporary.test: Import patch wl1012.patch mysql-test/t/rpl_trigger.test: Import patch wl1012.patch mysql-test/t/sp.test: Import patch wl1012.patch mysql-test/t/user_var-binlog.test: Import patch wl1012.patch mysys/Makefile.am: Import patch wl1012.patch mysys/base64.c: Import patch wl1012.patch sql/Makefile.am: Import patch wl1012.patch sql/ha_innodb.cc: Import patch wl1012.patch sql/ha_innodb.h: Import patch wl1012.patch sql/ha_partition.cc: Import patch wl1012.patch sql/handler.cc: Import patch wl1012.patch sql/handler.h: Import patch wl1012.patch sql/item_sum.cc: Import patch wl1012.patch sql/log.cc: Import patch wl1012.patch sql/log_event.cc: Import patch wl1012.patch sql/log_event.h: Import patch wl1012.patch sql/mysql_priv.h: Import patch wl1012.patch sql/mysqld.cc: Import patch wl1012.patch sql/rpl_filter.h: Import patch wl1012.patch sql/set_var.cc: Import patch wl1012.patch sql/share/errmsg.txt: Import patch wl1012.patch sql/slave.cc: Import patch wl1012.patch sql/slave.h: Import patch wl1012.patch sql/sp.cc: Import patch wl1012.patch sql/sp_head.cc: Import patch wl1012.patch sql/sql_acl.cc: Import patch wl1012.patch sql/sql_base.cc: Import patch wl1012.patch sql/sql_class.cc: Import patch wl1012.patch sql/sql_class.h: Import patch wl1012.patch sql/sql_delete.cc: Import patch wl1012.patch sql/sql_insert.cc: Import patch wl1012.patch sql/sql_lex.h: Import patch wl1012.patch sql/sql_list.h: Import patch wl1012.patch sql/sql_load.cc: Import patch wl1012.patch sql/sql_parse.cc: Import patch wl1012.patch sql/sql_plugin.cc: Import patch wl1012.patch sql/sql_rename.cc: Import patch wl1012.patch sql/sql_repl.h: Import patch wl1012.patch sql/sql_select.cc: Import patch wl1012.patch sql/sql_show.cc: Import patch wl1012.patch sql/sql_table.cc: Import patch wl1012.patch sql/sql_udf.cc: Import patch wl1012.patch sql/sql_union.cc: Import patch wl1012.patch sql/sql_update.cc: Import patch wl1012.patch sql/sql_yacc.yy: Import patch wl1012.patch sql/table.cc: Import patch wl1012.patch sql/table.h: Import patch wl1012.patch storage/innobase/include/lock0lock.h: Import patch wl1012.patch storage/innobase/include/row0mysql.h: Import patch wl1012.patch storage/innobase/include/row0vers.h: Import patch wl1012.patch storage/innobase/lock/lock0lock.c: Import patch wl1012.patch storage/innobase/row/row0mysql.c: Import patch wl1012.patch storage/innobase/row/row0sel.c: Import patch wl1012.patch storage/innobase/row/row0vers.c: Import patch wl1012.patch --- mysql-test/t/innodb_concurrent.test | 346 ++++++++++++++++++++++++++++++++++++ 1 file changed, 346 insertions(+) create mode 100644 mysql-test/t/innodb_concurrent.test (limited to 'mysql-test/t/innodb_concurrent.test') diff --git a/mysql-test/t/innodb_concurrent.test b/mysql-test/t/innodb_concurrent.test new file mode 100644 index 00000000000..1595fcd2467 --- /dev/null +++ b/mysql-test/t/innodb_concurrent.test @@ -0,0 +1,346 @@ +# +# Concurrent InnoDB tests, mainly in UPDATE's +# Bug#3300 +# Designed and tested by Sinisa Milivojevic, sinisa@mysql.com +# +# two non-interfering UPDATE's not changing result set +# + +# test takes circa 5 minutes to run, so it's big +-- source include/big_test.inc + +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; -- cgit v1.2.1