280 lines
12 KiB
Plaintext
280 lines
12 KiB
Plaintext
# Copyright (C) 2008-2009 Sun Microsystems, Inc. All rights reserved.
|
|
# Use is subject to license terms.
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; version 2 of the License.
|
|
#
|
|
# This program is distributed in the hope that it will be useful, but
|
|
# WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
# General Public License for more details.
|
|
#
|
|
# You should have received a copy of the GNU General Public License
|
|
# along with this program; if not, write to the Free Software
|
|
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301
|
|
# USA
|
|
|
|
#####################################################################
|
|
#
|
|
# Author: Jorgen Loland
|
|
# Date: April 2009
|
|
#
|
|
# Purpose: Implementation of WL#4218: Test that transactions executed
|
|
# concurrently with backup are either completely restored or not
|
|
# restored at all. No transaction should be partially represented
|
|
# after restore.
|
|
#
|
|
# Associated files:
|
|
# mysql-test/gentest/conf/invariant.yy
|
|
# mysql-test/gentest/conf/invariant.zz
|
|
# mysql-test/gentest/lib/GenTest/Validator/Invariant.pm
|
|
# mysql-test/gentest/lib/GenTest/Reporter/BackupAndRestoreInvariant.pm
|
|
#
|
|
#####################################################################
|
|
#
|
|
|
|
# Note 1: THIS TEST SCRIPT ASSUMES THAT THE INNODB OR FALCON STORAGE
|
|
# ENGINE IS USED.
|
|
#
|
|
# Note 2: THIS TEST SCRIPT ASSUMES THAT TRANSACTIONS THAT GET ERROR
|
|
# 1048 (INSERT RECORD WITH 'NULL' VALUE INTO A 'NOT NULL' COLUMN) IS
|
|
# ABORTED. THIS IS DONE BY Invariant.pm
|
|
#
|
|
# This script performs zero-sum transactions. There are two identical
|
|
# tables that store bank accounts for two different banks. Initially,
|
|
# there is a total of 1000 [1] bank account records with $100 each.
|
|
#
|
|
#
|
|
# Conceptual database schema:
|
|
#
|
|
# .-----------------. .-----------------.
|
|
# | bank1_accounts | | bank2_accounts |
|
|
# |-----------------| |-----------------|
|
|
# | *account_number | | *account_number |
|
|
# | balance | | balance |
|
|
# `-----------------' `-----------------'
|
|
#
|
|
# Actual database schema:
|
|
#
|
|
# .------------------------------. .------------------------------.
|
|
# | table490_innodb_int_autoinc | | table510_innodb_int_autoinc |
|
|
# |------------------------------| |------------------------------|
|
|
# | *pk | | *pk |
|
|
# | int_not_null | | int_not_null |
|
|
# `------------------------------' `------------------------------'
|
|
#
|
|
# The table names vary with different storage engines, so they
|
|
# should not be used as constants. To select a random table, use rule
|
|
# "pick_tbl".
|
|
#
|
|
# Each transaction performs multiple insert/update/delete operations.
|
|
# Any transaction that commits has to follow this rule:
|
|
#
|
|
# Any money withdrawn from an account has to be deposited
|
|
# in another account, possibly in the other bank.
|
|
#
|
|
# Transactions that abort are allowed to not follow this rule.
|
|
#
|
|
# invariant.[yy|zz] are accompanied with validator Invariant.pm [2]
|
|
# and reporter BackupAndRestoreInvariant.pm [3]. When executed
|
|
# together, these modules check that BACKUP/RESTORE is transactionally
|
|
# consistent: Any transaction executing concurrently with backup is
|
|
# either completely included in the database after restore, or is not
|
|
# included at all.
|
|
#
|
|
# [1] The two tables have 490 and 510 records initially (a small
|
|
# difference only so that RQG will generate two different table
|
|
# names). If the initial number of records is changed in invariant.zz,
|
|
# upd_range should likely be changed as well.
|
|
#
|
|
# [2] Checks that the total account balance remains the same and rolls
|
|
# back transactions that need to be aborted
|
|
#
|
|
# [3] Performs BACKUP and RESTORE, and checks that the total account
|
|
# balance is correct after restore.
|
|
|
|
query_init:
|
|
SET AUTOCOMMIT=OFF ;
|
|
|
|
query:
|
|
commit_trans |
|
|
commit_trans |
|
|
commit_trans |
|
|
commit_trans |
|
|
abort_trans ;
|
|
|
|
commit_trans:
|
|
START TRANSACTION ; body ; COMMIT ;
|
|
|
|
abort_trans:
|
|
START TRANSACTION ; body ; ROLLBACK ;
|
|
|
|
body:
|
|
update_two |
|
|
update_two_scan |
|
|
update_four |
|
|
update_sixteen |
|
|
update_sixteen_sleep |
|
|
delete_insert_two |
|
|
insert_two |
|
|
delete_update |
|
|
# delete_insert_two commits, but before doing any write operation.
|
|
# Safe to run updates *after* delete_i_t, but not reverse order
|
|
delete_insert_two ; update_four ; update_four ;
|
|
|
|
# The Robin Hood script, pt1
|
|
# Delete one of the records with highest account balance and deposit the
|
|
# same amount to another record with a low account balance.
|
|
# WARNING: A "COMMIT ; START TRANSACTION" is part of this script
|
|
# See documentation for delete_insert_two
|
|
# NOTE: The final delete must be performed on the same record (and
|
|
# therefore same table) as @val is selected from. Hence the use of
|
|
# $tbl_cnst for this part of the script.
|
|
delete_update:
|
|
prepare_delete ; UPDATE $tbl_cnst SET `col_int_not_null`=`col_int_not_null`+1 WHERE pk=@delpk ; SELECT @val:=`col_int_not_null` FROM $tbl_cnst WHERE pk=@delpk ; SET @val=@val-1 ; update_low_randtbl ; DELETE from $tbl_cnst WHERE pk=@delpk ; check_val_not_null ;
|
|
|
|
# Insert a record with int_not_null=@val and delete it. This will make
|
|
# the transaction abort if @val=null
|
|
check_val_not_null:
|
|
insert_one ; SELECT @ins_id:=LAST_INSERT_ID() ; DELETE from $tbl WHERE pk=@ins_id ;
|
|
|
|
# The Robin Hood script, pt2
|
|
# Delete one of the records with highest account balance and deposit the
|
|
# same total amount to two new records.
|
|
# WARNING: A "COMMIT ; START TRANSACTION" is part of this script
|
|
# NOTE: Since we get @delpk by selecting from the table, we must start
|
|
# a new transaction (done in prepare_delete). Otherwise, due to
|
|
# multiversion concurrency control, "select @val ..." will get an old
|
|
# value if the @delpk record is updated or deleted in a concurrent
|
|
# thread. This results in missing update anomaly.
|
|
# NOTE: @val must be set to NULL initially, otherwise it retains old
|
|
# values if the @delpk record is deleted by a concurrent thread (since
|
|
# the result set is empty in this case). By setting @val to NULL, the
|
|
# following inserts will be rejected by the server and thus have no
|
|
# effect.
|
|
# NOTE: The first update (+1) is performed to set a lock. Without this
|
|
# update, the following select may get an old value after a concurrent
|
|
# thread has deleted the record.
|
|
# NOTE: The final delete must be performed on the same record (and
|
|
# therefore same table) as @val is selected from. Hence the use of
|
|
# $tbl_cnst for this part of the script.
|
|
delete_insert_two:
|
|
prepare_delete ; UPDATE $tbl_cnst SET `col_int_not_null`=`col_int_not_null`+1 WHERE pk=@delpk ; SELECT @val:=`col_int_not_null`-1 FROM $tbl_cnst WHERE pk=@delpk ; insert_two_valsum ; DELETE from $tbl_cnst WHERE pk=@delpk ;
|
|
|
|
# Perform neccessary steps before a record is deleted.
|
|
# RETURN $tbl_cnst
|
|
# RETURN @delpk
|
|
prepare_delete:
|
|
SET @val=NULL ; pick_tbl_const ; set_delpk ; COMMIT ; START TRANSACTION ;
|
|
|
|
# Add two records with a total account balance of @val
|
|
# NOTE: SET @val=@val-@val+@myprime must not be changed to
|
|
# @val=@myprime because @val must remain NULL if it was NULL before
|
|
# the assignment. This prevents insert of a record with account
|
|
# balance @myprime in the case where @delpk has been deleted by a
|
|
# concurrent thread.
|
|
insert_two_valsum:
|
|
SET @myprime=prime ; SET @val=@val-@myprime ; insert_one_randtbl ; SET @val=@val-@val+@myprime ; insert_one_randtbl ;
|
|
|
|
# Add two records, one with positive and one with negative account
|
|
# balance. The balance is chosen randomly.
|
|
insert_two:
|
|
SET @val=prime ; insert_one_randtbl ; SET @val=-@val ; insert_one_randtbl ;
|
|
|
|
# Add one record with account balance defined by @val into a random table
|
|
insert_one_randtbl:
|
|
pick_tbl ; insert_one ;
|
|
|
|
# Add one record with account balance defined by @val into $tbl
|
|
insert_one:
|
|
INSERT INTO $tbl(`col_int_not_null`) VALUES (@val) ;
|
|
|
|
# Update two records by withdrawing from one account and depositing
|
|
# into the other. The amount moved between accounts is chosen
|
|
# randomly.
|
|
update_two:
|
|
SET @val=prime ; update_low_randtbl ; SET @val=-@val ; update_high_randtbl ;
|
|
|
|
# Update four records by depositing into three accounts and
|
|
# withdrawing the total from one account. This way, updates are not
|
|
# always symmetric (increase and decrease the same amount). Each
|
|
# updated account is chosen on random from a random bank.
|
|
update_four:
|
|
SET @val=prime ; SET @total=@val ; update_low_randtbl ; SET @val=prime ; SET @total=@total+@val ; update_low_randtbl ; SET @val=prime ; SET @total=@total+@val ; update_low_randtbl ; SET @val=-@total ; update_high_randtbl ;
|
|
|
|
update_sixteen:
|
|
update_four ; update_four ; update_four ; update_four ;
|
|
|
|
# Significantly increase execution time of 16 updates by sleeping for
|
|
# a total of 3 seconds.
|
|
update_sixteen_sleep:
|
|
update_four ; SELECT SLEEP(1); update_four ; SELECT SLEEP(1); update_four ; SELECT SLEEP(1); update_four ; SELECT SLEEP(1);
|
|
|
|
# Update one of the records with lowest account balance by adding @val to it
|
|
# Requirement for use: @val must have an integer value
|
|
update_low_randtbl:
|
|
pick_tbl ; set_updpk_low ; UPDATE $tbl SET `col_int_not_null`=`col_int_not_null`+(@val) WHERE pk=@updpk ;
|
|
|
|
# Update one of the records with highest account balance by adding @val to it
|
|
# Requirement for use: @val must have an integer value
|
|
update_high_randtbl:
|
|
pick_tbl ; set_updpk_high ; UPDATE $tbl SET `col_int_not_null`=`col_int_not_null`+(@val) WHERE pk=@updpk ;
|
|
|
|
update_two_scan:
|
|
SET @val=prime ; update_low_randtbl_scan ; SET @val=-@val ; update_high_randtbl_scan ;
|
|
|
|
# Update one for the records with lowest account balance by adding
|
|
# @val to it. The update has to perform a table scan to find the
|
|
# record to update.
|
|
update_low_randtbl_scan:
|
|
pick_tbl ; UPDATE $tbl SET `col_int_not_null`=`col_int_not_null`+@val WHERE pk IN (SELECT pk FROM (SELECT pk FROM $tbl WHERE `col_int_not_null` IN (SELECT MIN(`col_int_not_null`) FROM $tbl WHERE pk<=upd_range)) AS tmp1) ORDER BY rand() LIMIT 1;
|
|
|
|
# Update one for the records with highest account balance by adding
|
|
# @val to it. The update has to perform a table scan to find the
|
|
# record to update.
|
|
update_high_randtbl_scan:
|
|
pick_tbl ; UPDATE $tbl SET `col_int_not_null`=`col_int_not_null`+@val WHERE pk IN (SELECT pk FROM (SELECT pk FROM $tbl WHERE `col_int_not_null` IN (SELECT MAX(`col_int_not_null`) FROM $tbl WHERE pk<=upd_range)) AS tmp1) ORDER BY rand() LIMIT 1;
|
|
# Before "where pk<=" was moved inside innermost select, the subselect would sometimes return an empty set
|
|
# pick_tbl ; UPDATE $tbl SET `col_int_not_null`=`col_int_not_null`+@val WHERE pk IN (SELECT pk FROM (SELECT pk FROM $tbl WHERE pk<=upd_range AND `col_int_not_null` IN (SELECT MAX(`col_int_not_null`) FROM $tbl)) AS tmp1) ORDER BY rand() LIMIT 1;
|
|
|
|
# Get one primary key of an existing record in the update range of the records
|
|
# RETURN @updpk
|
|
set_updpk_low:
|
|
SELECT @updpk:=`pk` FROM (pk_upd_low) AS tbl ;
|
|
|
|
set_updpk_high:
|
|
SELECT @updpk:=`pk` FROM (pk_upd_high) AS tbl ;
|
|
|
|
pk_upd_low:
|
|
SELECT `pk` FROM $tbl WHERE `pk` <= upd_range ORDER BY `col_int_not_null` ASC LIMIT prime ;
|
|
|
|
pk_upd_high:
|
|
SELECT `pk` FROM $tbl WHERE `pk` <= upd_range ORDER BY `col_int_not_null` DESC LIMIT prime ;
|
|
|
|
# Get one primary key of an existing record in the insert/delete range of the records
|
|
# RETURN @delpk
|
|
set_delpk:
|
|
SELECT @delpk:=`pk` FROM $tbl_cnst WHERE `pk` > upd_range ORDER BY `col_int_not_null` DESC LIMIT prime ;
|
|
|
|
# Pick a random table to operate on
|
|
# RETURN $tbl
|
|
pick_tbl:
|
|
{ $tbl = $prng->arrayElement($executors->[0]->tables()) ; return undef ; } ;
|
|
|
|
# For transactions that need to operate on the same table more than
|
|
# once. By calling this rule one time only for a transaction,
|
|
# $tbl_cnst will remain unchanged for later use withing that transaction.
|
|
# RETURN $tbl_cnst
|
|
pick_tbl_const:
|
|
{ $tbl_cnst = $prng->arrayElement($executors->[0]->tables()) ; return undef ; } ;
|
|
|
|
# Update operations are performed on records with a pk lower than this
|
|
# number, while delete operations are performed on records with a
|
|
# higher pk.
|
|
upd_range:
|
|
250 ;
|
|
|
|
prime:
|
|
1 | 2 | 3 | 5 | 7 | 11 | 13 | 17 | 23 | 29 | 31 ;
|