From 255538e947f55a3c7a8aeb520a258653dbbd4810 Mon Sep 17 00:00:00 2001 From: drh Date: Wed, 27 Aug 2008 18:56:36 +0000 Subject: [PATCH] Add the trans2.test script designed to stress statement rollback and no-write optimizations on large transactions with a small cache. (CVS 5627) FossilOrigin-Name: 39c34e2238c27b2a2f4f0b896126ccbd299114c5 --- manifest | 13 +-- manifest.uuid | 2 +- test/quick.test | 3 +- test/trans2.test | 232 +++++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 242 insertions(+), 8 deletions(-) create mode 100644 test/trans2.test diff --git a/manifest b/manifest index 59bc3b162e..36736b4c98 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Do\snot\smark\spages\sas\sclean\swhen\sdoing\sa\sstatement\sjournal\srollback.\s(CVS\s5626) -D 2008-08-27T18:03:20 +C Add\sthe\strans2.test\sscript\sdesigned\sto\sstress\sstatement\srollback\sand\nno-write\soptimizations\son\slarge\stransactions\swith\sa\ssmall\scache.\s(CVS\s5627) +D 2008-08-27T18:56:36 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0 F Makefile.in 689e14735f862a5553bceef206d8c13e29504e44 F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654 @@ -450,7 +450,7 @@ F test/pragma2.test 5364893491b9231dd170e3459bfc2e2342658b47 F test/printf.test 262a5acd3158f788e9bdf7f18d718f3af32ff6ef F test/progress.test 5b075c3c790c7b2a61419bc199db87aaf48b8301 x F test/ptrchng.test ef1aa72d6cf35a2bbd0869a649b744e9d84977fc -F test/quick.test 3dba257b9d4e06e1b0199bc1401052f554ab5f14 +F test/quick.test 4e702221947cf31a624f929502e55871bcb11dbc F test/quote.test 215897dbe8de1a6f701265836d6601cc6ed103e6 F test/rdonly.test bd054831f8a3078e765a0657e247182486f0cb47 F test/reindex.test 44edd3966b474468b823d481eafef0c305022254 @@ -554,6 +554,7 @@ F test/tkt3334.test ea13a53cb176e90571a76c86605b14a09efe366d F test/tokenize.test ce430a7aed48fc98301611429595883fdfcab5d7 F test/trace.test 951cd0f5f571e7f36bf7bfe04be70f90fb16fb00 F test/trans.test 2fd24cd7aa0b879d49a224cbd647d698f1e7ac5c +F test/trans2.test d5337e61de45e66b1fcbf9db833fa8c82e624b22 F test/trigger1.test 7c13f39ca36f529bf856e05c7d004fc0531d48b4 F test/trigger2.test 33bf8ae9b788013be194efe5f66c04a202facbb4 F test/trigger3.test 501b8489eb6b9cb5b005f60b071583c01a3c3041 @@ -623,7 +624,7 @@ F tool/speedtest16.c c8a9c793df96db7e4933f0852abb7a03d48f2e81 F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 1dbced29de5f59ba2ebf877edcadf171540374d1 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e -P b01c65b065c62e3dd71e88866a953668b5e2f25f -R 689d729100f20090b1e466716c44a30f +P 9d7722f4f357b96c37f950024f1e7e160e2c1252 +R 7e38426936c8ee7d92dffd15d06dbe75 U drh -Z 88ce9a46e91a9c5f4a11c03c806a9437 +Z 4f34f6e09d3a22e5fabb341c54267054 diff --git a/manifest.uuid b/manifest.uuid index 06d5901362..c451fdd21e 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -9d7722f4f357b96c37f950024f1e7e160e2c1252 \ No newline at end of file +39c34e2238c27b2a2f4f0b896126ccbd299114c5 \ No newline at end of file diff --git a/test/quick.test b/test/quick.test index a4d25903ca..7e90c32b9b 100644 --- a/test/quick.test +++ b/test/quick.test @@ -6,7 +6,7 @@ #*********************************************************************** # This file runs all tests. # -# $Id: quick.test,v 1.84 2008/06/26 08:29:35 danielk1977 Exp $ +# $Id: quick.test,v 1.85 2008/08/27 18:56:36 drh Exp $ proc lshift {lvar} { upvar $lvar l @@ -76,6 +76,7 @@ set EXCLUDE { tkt2686.test thread001.test thread002.test + trans2.test vacuum3.test incrvacuum_ioerr.test diff --git a/test/trans2.test b/test/trans2.test new file mode 100644 index 0000000000..cfd3e67736 --- /dev/null +++ b/test/trans2.test @@ -0,0 +1,232 @@ +# 2008 August 27 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file implements regression tests for SQLite library. The +# focus of this script is transactions +# +# $Id: trans2.test,v 1.1 2008/08/27 18:56:36 drh Exp $ +# +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# A procedure to scramble the elements of list $inlist into a random order. +# +proc scramble {inlist} { + set y {} + foreach x $inlist { + lappend y [list [expr {rand()}] $x] + } + set y [lsort $y] + set outlist {} + foreach x $y { + lappend outlist [lindex $x 1] + } + return $outlist +} + +# Generate a UUID using randomness. +# +expr srand(1) +proc random_uuid {} { + set u {} + for {set i 0} {$i<5} {incr i} { + append u [format %06x [expr {int(rand()*16777216)}]] + } + return $u +} + +# Compute hashes on the u1 and u2 fields of the sample data. +# +proc hash1 {} { + global data + set x "" + foreach rec [lsort -integer -index 0 $data] { + append x [lindex $rec 1] + } + return [md5 $x] +} +proc hash2 {} { + global data + set x "" + foreach rec [lsort -integer -index 0 $data] { + append x [lindex $rec 3] + } + return [md5 $x] +} + +# Create the initial data set +# +unset -nocomplain data i max_rowid todel n rec max1 id origres newres +unset -nocomplain inssql modsql s j z +set data {} +for {set i 0} {$i<400} {incr i} { + set rec [list $i [random_uuid] [expr {int(rand()*5000)+1000}] [random_uuid]] + lappend data $rec +} +set max_rowid [expr {$i-1}] + +# Create the T1 table used to hold test data. Populate that table with +# the initial data set and check hashes to make sure everything is correct. +# +do_test trans2-1.1 { + execsql { + PRAGMA cache_size=100; + CREATE TABLE t1( + id INTEGER PRIMARY KEY, + u1 TEXT UNIQUE, + z BLOB NOT NULL, + u2 TEXT UNIQUE + ); + } + foreach rec [scramble $data] { + foreach {id u1 z u2} $rec break + db eval {INSERT INTO t1 VALUES($id,$u1,zeroblob($z),$u2)} + } + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} +} [list [hash1] [hash2]] + +# Repeat the main test loop multiple times. +# +for {set i 2} {$i<=30} {incr i} { + # Delete one row out of every 10 in the database. This will add + # many pages to the freelist. + # + set todel {} + set n [expr {[llength $data]/10}] + set data [scramble $data] + foreach rec [lrange $data 0 $n] { + lappend todel [lindex $rec 0] + } + set data [lrange $data [expr {$n+1}] end] + set max1 [lindex [lindex $data 0] 0] + foreach rec $data { + set id [lindex $rec 0] + if {$id>$max1} {set max1 $id} + } + set origres [list [hash1] [hash2]] + do_test trans2-$i.1 { + db eval "DELETE FROM t1 WHERE id IN ([join $todel ,])" + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $origres + integrity_check trans2-$i.2 + + # Begin a transaction and insert many new records. + # + set newdata {} + foreach id $todel { + set rec [list $id [random_uuid] \ + [expr {int(rand()*5000)+1000}] [random_uuid]] + lappend newdata $rec + lappend data $rec + } + for {set j 1} {$j<50} {incr j} { + set id [expr {$max_rowid+$j}] + lappend todel $id + set rec [list $id [random_uuid] \ + [expr {int(rand()*5000)+1000}] [random_uuid]] + lappend newdata $rec + lappend data $rec + } + set max_rowid [expr {$max_rowid+$j-1}] + set modsql {} + set inssql {} + set newres [list [hash1] [hash2]] + do_test trans2-$i.3 { + db eval BEGIN + foreach rec [scramble $newdata] { + foreach {id u1 z u2} $rec break + set s "INSERT INTO t1 VALUES($id,'$u1',zeroblob($z),'$u2');" + append modsql $s\n + append inssql $s\n + db eval $s + } + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $newres + integrity_check trans2-$i.4 + + # Do a large update that aborts do to a constraint failure near + # the end. This stresses the statement journal mechanism. + # + do_test trans2-$i.10 { + catchsql { + UPDATE t1 SET u1=u1||'x', + z = CASE WHEN id<$max_rowid + THEN zeroblob((random()&65535)%5000 + 1000) END; + } + } {1 {t1.z may not be NULL}} + do_test trans2-$i.11 { + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $newres + + # Delete all of the newly inserted records. Verify that the database + # is back to its original state. + # + do_test trans2-$i.20 { + set s "DELETE FROM t1 WHERE id IN ([join $todel ,]);" + append modsql $s\n + db eval $s + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $origres + + # Do another large update that aborts do to a constraint failure near + # the end. This stresses the statement journal mechanism. + # + do_test trans2-$i.30 { + catchsql { + UPDATE t1 SET u1=u1||'x', + z = CASE WHEN id<$max1 + THEN zeroblob((random()&65535)%5000 + 1000) END; + } + } {1 {t1.z may not be NULL}} + do_test trans2-$i.31 { + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $origres + + # Redo the inserts + # + do_test trans2-$i.40 { + db eval $inssql + append modsql $inssql + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $newres + + # Rollback the transaction. Verify that the content is restored. + # + do_test trans2-$i.90 { + db eval ROLLBACK + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $origres + integrity_check trans2-$i.91 + + # Repeat all the changes, but this time commit. + # + do_test trans2-$i.92 { + db eval BEGIN + catchsql { + UPDATE t1 SET u1=u1||'x', + z = CASE WHEN id<$max1 + THEN zeroblob((random()&65535)%5000 + 1000) END; + } + db eval $modsql + catchsql { + UPDATE t1 SET u1=u1||'x', + z = CASE WHEN id<$max1 + THEN zeroblob((random()&65535)%5000 + 1000) END; + } + db eval COMMIT + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} + } $newres + integrity_check trans2-$i.93 +} + +unset -nocomplain data i max_rowid todel n rec max1 id origres newres +unset -nocomplain inssql modsql s j z +finish_test -- 2.47.2