Brad,
Here are my results after modifying the "begin transaction" to a "begin
exclusive"
Begin transaction is a bit Lazy in that the lock escalation doesnt occur until
the pager escalates the lock due to a write.
You'll see that the begin exclusive acquires a lock immediately and avoids the
behavoir.
Creating a table
0 => Executing: CREATE TABLE test_table(threadnum INT, cnt INT, testcol TEXT)
0 => started ....
1 => started ....
all threads started
0 => Executing: BEGIN EXCLUSIVE
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 0, 'test0_0')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 1, 'test0_1')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 2, 'test0_2')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 3, 'test0_3')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 4, 'test0_4')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 5, 'test0_5')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 6, 'test0_6')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 7, 'test0_7')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 8, 'test0_8')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 9, 'test0_9')
1 => Executing: BEGIN EXCLUSIVE
1 => BUSY
0 => Executing: COMMIT
0 => finished.
1 => Executing: BEGIN EXCLUSIVE
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => Executing: INSERT INTO test_table VALUES(1, 1, 'test1_1')
1 => Executing: INSERT INTO test_table VALUES(1, 2, 'test1_2')
1 => Executing: INSERT INTO test_table VALUES(1, 3, 'test1_3')
1 => Executing: INSERT INTO test_table VALUES(1, 4, 'test1_4')
1 => Executing: INSERT INTO test_table VALUES(1, 5, 'test1_5')
1 => Executing: INSERT INTO test_table VALUES(1, 6, 'test1_6')
1 => Executing: INSERT INTO test_table VALUES(1, 7, 'test1_7')
1 => Executing: INSERT INTO test_table VALUES(1, 8, 'test1_8')
1 => Executing: INSERT INTO test_table VALUES(1, 9, 'test1_9')
1 => Executing: COMMIT
1 => finished.
exiting...(test succeeded)
