Take the 2-minute tour ×


I want to get data into a database on a multicore system with ative WAL using JDBC. I was thinking about spawning multiple threads in my application to insert data parallely.

If the application has multiple threads I will have to increase the isolation level to Repeatable Read which on MVCC-databases should be mapped to Snapshot isolation.

If I were using one thread I wouldn't need isolation levels. As far as I know most Snapshot isolation databases analyze the write sets of all transaction that could have a conflict and then rollback all but one of the real conflict transactions. More specific I'm talking about Oracle, InnoDB and PostgreSQL.

1.) Is this analyzing of the write sets expensive?

2.) Is it a good idea to multithread the inserts for a higher total throughput? Real conflict are nearly impossible because of the application layer feeding the threads conflict free stuff. But the database shall be a safety net.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Oracle does not support Repeatable Read. It supports only Read Committed and Serializable. I might be mistaken, but setting an isolation level of Repeatable Read for Oracle might result in a transaction with an isolation level of Serializable. In short, you are left to mercy of the database support for the isolation levels that you desire.

I cannot speak for InnoDB and PostgreSQL, but the same would apply if they do not support the required isolation levels. The database could automatically upgrade the isolation level to a higher level to meet the desired isolation characteristics. You ought to rethink this approach, if your application's desired isolation level has to be Repeatable Read.

The problem like you've rightly inferred is that optimistic locking will possibly result in transaction rollbacks, if a conflict is detected. Oracle does so by reporting the ORA-08177 SQL error. Since this error is reported when two threads will access the same data range, it could be avoided if the threads work against data sets involving different data ranges. You will have to ensure that this is the case when dividing work across threads.

share|improve this answer
What Oracle calls Serializable is really Snapshot isolation, or am I mistaken? I don't think any of these databases has real Serializable. –  Franz Kafka Jun 14 '11 at 13:11
It is snapshot isolation in that the SCNs (System Change Numbers) at the time of the read/update is compared to the one at the beginning of the transaction. If it detects a change, it throws the ORA-08177 error. –  Vineet Reynolds Jun 14 '11 at 13:16
Okay for my multithread system I would need Orcales Serializable. Will that still be cheaper than one thread? –  Franz Kafka Jun 14 '11 at 13:28
Can't say from my armchair :) You should profile this, for it will be performance hotspot in either case. –  Vineet Reynolds Jun 14 '11 at 13:32
add comment

I think the limiting factor here will be disk IO, not the overhead of moving to Repeatable Read.

Even a single thread may be able to max out the disks on the DB server especially with the amount of DB logging required on insert / update. Are you sure that's not already the case?

Also, in any multi-user system, you probably want to be running with Repeatable Read isolation anyway (Postgres only supports this and serializable). So, I don't think of this as adding any "overhead" above what I would normally see.

share|improve this answer
The server will be scaled to handle the WAL, the data can stay in memory (hopefully). The checkpoint interval will be set very high, so that WAL can use the disk on its own. –  Franz Kafka Jun 14 '11 at 13:38
add comment

Your Answer


