Set INITRANS in Oracle schema creation script to support default SERIALIZABLE JobRepository [BATCH-1328]

[spring-projects-issues]

Ian Brandt opened BATCH-1328 and commented

See the referenced forum post. Per http://forum.springsource.org/showpost.php?p=249913&postcount=7 an INITRANS value of at least 3 is required for transactions with SERIALIZABLE isolation level. As that is the default isolation level for SimpleJobRepository as created by AbstractJobRepositoryFactoryBean it would be nice to have a minimal INITRANS set in the sample schema creation script. Especially since I just discovered you can hit an exception even with the SyncTaskExecutor when running jobs in rapid succession.

I'd be happy to submit a patch for this in the near future, but I notice the source for 'spring-batch-core/src/main/resources/schema-oracle10g.sql' says, "-- Autogenerated: do not edit this file". Any pointers on where the source of that source is would help grease the process, or is it something someone is maintaining outside of VC? A quick search for *.sql yielded no clues. Thanks.

---

Affects: 2.0.1

Reference URL: http://forum.springsource.org/showthread.php?p=249913

[spring-projects-issues]

Dave Syer commented

The scripts are in SVN under src/main/sql. No need to provide a patch (if we want to do this it is trivial). From what I can tell setting INITRANS would affect all access to the table, so we probably only want to put it on the BATCH_JOB_EXECUTION table (since others should be affected by default)?

[spring-projects-issues]

Ian Brandt commented

As INITRANS is per table I believe you'll need this on BATCH_JOB_INSTANCE as well, as isn't it inserted into within the same serializable transaction as the execution? Actually INITRANS would need to be be set for any index of either table too, so a little comment to that effect might also be handy. (We haven't created indexes for either yet.)

[spring-projects-issues]

Dave Syer commented

I guess BATCH_JOB_INSTANCE and BATCH_JOB_PARAMS would both normally get an insert (unless it was a restart), and potentially BATCH_JOB_EXECUTION_CONTEXT as well. I'm hesitant to make this the default if it is going to hit a large fraction of the schema without understanding the implications a bit more. If you have time to try it, I'd be interested in hearing what the result is, especially if you can try different combinations of tables. We already intentionally don't add indexes and other DBA stuff to our sample DDL scripts. This could just be another suggested addition in the documentation for people using Oracle.

[spring-projects-issues]

Mahmoud Ben Hassine commented

The provided DDLs are a starting point and as mentioned in the Meta-Data Schema section of the reference documentation, some optimizations (like index declarations) are not included in those DDLs by default.

The INITRANS for oracle could be one of them, but we can add a note about it in that section as Dave Syer mentioned. I'm tagging this ticket as a documentation improvement.

[esoni]

hi @spring-issuemaster spring-issuemaster can you please link the script suggested for oracle for transactions with SERIALIZABLE isolation level ?
Thanks