Contains concurrency tests for Farrago.
Revision $Id: //open/dev/farrago/src/net/sf/farrago/test/concurrent/package.html#7 $
Copyright Copyright (C) 2005-2009 The Eigenbase Project
Copyright (C) 2005-2009 SQLstream, Inc.
Copyright (C) 2005-2009 LucidEra, Inc.
Author Stephan Zuercher

Farrago Scripted Concurrency Testing

Farrago Scripted Concurrency Testing is based on {@link net.sf.farrago.test Farrago Unit Testing}.

Scripts

As in the single-threaded diff-based testing, the test writers will need to concern themselves with 3 files:

The multi-threaded case the test script ends in .mtsql to make it easier distinguish it from single-threaded test scripts.

Keywords in the script denote special actions to be taken by the test harness (repeating a sequence of SQL statements, setting a query timeout, etc.). Keywords start with an @.

The multi-threaded test script is divided into sections, one for test setup (e.g. SQL that creates schemas, tables, etc. that must be in place before the multi-threaded testing begins), and one for each thread in the test. The setup section can be omitted in its entirety if it's not needed. Here's an example test script:

@nolockstep

@setup
	CREATE SCHEMA s;
	CREATE TABLE s.t (c INTEGER NOT NULL PRIMARY KEY);
@end

@thread first_thread
	select * from s.t where c < 5;
@end

@thread second_thread
	select * from s.t where c >= 5;
@end

Keywords

Keyword Description
@lockstep
@nolockstep
Controls whether execution of each command in the script is synchronized. These keywords must appear at the start of the file (before @setup or @thread), or not at all. If neither keyword is present, the default is @nolockstep.
@disabled
@enabled
Controls whether execution of the test is disabled or not. These keywords must appear at the start of the file (before @setup or @thread), or not at all. If neither keyword is present, the default is @enabled.
@setup
...
@end
Defines SQL statements that will execute before the multi-threaded portion of the test is started. Optional.
@thread X[,Y,...]
...
@end
Defines one or more threads of execution and their SQL commands. Note that if multiple thread names (X, Y, ...) are given, multiple threads will execute the same commands. At least one @thread block must appear in the script. If only one appears, you should probably be using the old single-threaded testing.
@repeat N
...
@end
Specifies that the given commands should be repeated with N times. May only appear inside of a @thread block.
@sync Specifies that the given thread should block and wait for all other threads to reach their @sync commands. The same number of @sync keywords must appear in each thread once all loops have been unrolled. @sync can only be used with @nolockstep. May only appear inside of a @thread or @repeat block.
@timeout T SQL Execute the given SQL with a query timeout of T milliseconds. The statement is prepared and executed, all results are fetched and written to the .ref file, and the statement is closed. May only appear inside of a @thread or @repeat block.
@prepare SQL
@fetch [T]
@close
Prepare the given SQL. Execute and fetch the results with an optional query timeout of T milliseconds, writing them into the .ref file. Close the statement. Only the @sync or @sleep keywords may appear between these three keywords. May only appear inside of a @thread or @repeat block.
@sleep T Sleep for T milliseconds. May only appear inside of a @thread or @repeat block.
@err SQL Execute SQL and verify that it results in an error.

SQL statements can appear anywhere within the @setup and @thread blocks. Any statement beginning with select is executed as a JDBC query. Statements beginning with insert, update or delete are executed as JDBC updates. Everything else is executed as DDL. Whitespace is ignored at the beginning and end of a statement. Statements are delimited with semicolons, even when preceded by a keyword. Keywords must be the first thing (except for whitespace) that appers on a line. Double dashes indicate comments. For example:

select * from sales.depts;                 -- good

@timeout 5000 select * from sales.depts;   -- good

@timeout 5000 select *
from sales.depts;                          -- good

@timeout 5000 select * from sales.depts    -- bad, missing semicolon
select * from sales.emps;

select * from sales.depts                  -- bad, missing semicolon
@timeout 5000 select * from sales.emps;

select * from sales.depts; @sync           -- bad, sync must be on next line

Example Script

-- redundant:
@nolockstep

-- Two threads reading the same data.
@thread 1,2
	-- pre execute the SQL to prime the pumps
	@timeout 1000 select * from sales.bids;

	@prepare select * from sales.bids;

	-- rendezvous with writer thread
	@sync
	@fetch 15000
	@sync
	@close	
@end

@thread writer
	-- rendezvous with reader threads
	@sync
	@sleep 5000
	insert into sales.bids
		values(1,  'ORCL', 100, 12.34,     10000, 'Oracle at 12.34');
	commit;
	insert into sales.bids
		values(2,  'MSFT', 101, 23.45,     20000, 'Microsoft at 23.45');
	commit;

	-- real test has more inserts here

	@sync
@end

Example Output File

The output from each thread is stored in a temporary file until the test completes. At that point, the files are merged together into a single .log file containing the results of each thread, in the order the threads were defined. The output for the example script looks like:
-- thread 1
> select * from sales.bids;
+---------+------------+
| DEPTNO  |    NAME    |
+---------+------------+
| 10      | Sales      |
| 20      | Marketing  |
| 30      | Accounts   |
+---------+------------+
>
> select * from sales.bids;
+---------+------------+
| DEPTNO  |    NAME    |
+---------+------------+
| 10      | Sales      |
| 20      | Marketing  |
| 30      | Accounts   |
+---------+------------+

-- end of thread 1

-- thread 2
> select * from sales.bids;
+---------+------------+
| DEPTNO  |    NAME    |
+---------+------------+
| 10      | Sales      |
| 20      | Marketing  |
| 30      | Accounts   |
+---------+------------+
>
> select * from sales.bids;
+---------+------------+
| DEPTNO  |    NAME    |
+---------+------------+
| 10      | Sales      |
| 20      | Marketing  |
| 30      | Accounts   |
+---------+------------+

-- end of thread 2

-- thread writer
> insert into sales.bids
>	values(1,  'ORCL', 100, 12.34,     10000, 'Oracle at 12.34');
1 row affected.
> commit;
> insert into sales.bids
>	values(2,  'MSFT', 101, 23.45,     20000, 'Microsoft at 23.45');
1 row affected.
> commit;
-- end of thread writer
(Yes the results of the select statements are obviously wrong.)

Open Issues