2
Create 2 tables with a primary key and 3 integer columns. Both get the same rows (1,123,1,123),(2,124,2,124),(3,125,3,125),(4,126,4,126). The second and third column may get an index to have cases with, without and mutilple index. Create views on the tables. Create an update trigger. Create a stored procedure updating the table. Create a stored function updating the table and deliver the key as result.
4
The test isself consists of 2 sessions (transactions) running in "parallel" (same user "root") accessing and locking the same tables on basis of a row lock. Expected is that both sessions(transactions) can update the table successfully.
7
execute an explain to every select and one of the following selects on the first half of table t1:
8
- select <non index columns> ... where ... for update;
9
- select <non index columns> ... where ... lock in share mode;
10
- select <indexed columns> ... where ... for update;
11
- select <indexed columns> ... where ... lock in share mode;
12
- select <indexed columns> ... ignore index ... where ... for update;
13
- select <indexed columns> ... ignore index ... where ... lock in share mode;
14
- select ... where (select...) ... for update;
15
- select ... where (select...) ... lock in share mode;
16
- (select ... where) union (select ... where) for update;
17
- (select ... where) union (select ... where) lock in...;
18
- select <view> ... where ... for update;
19
- select <view> ... where ... lock in ...;
20
- select <join> ... where ... for update;
21
- select <join> ... where ... lock in ...;
25
- trigger accessing table t1
26
- stored procedure accessing table t1
27
- stored function accessing table t1
30
executes the same on the last half of table t1
32
call of mysqld with option
33
--innodb_locks_unsafe_for_binlog
35
As the tests above work with small tables (<10 rows) there must be at least one test with a big table (>1000 rows) doing a table scan.