Job JobSchedulerCleanupHistory locks database tables and blocks JobScheduler

The job JobschedulerCleanup job deletes multiple records from the SCHEDULER_HISTORY, ORDER_HISTORY and other Jobscheduler tables.
The following behavior is observed:

1. job compares the dates of records to be deleted in Java over Hibernate over filter
2. job starts to many open transactions in Java code
3. job does explicit commit in the delete method
4. the time consumption of the delete operation can be improved using the following methods:
   • replace hibernate with SQL statement, here the challenge is to find SQL statement compatible with all supported databases
   • drop indexes and constrains
   • set explicit session level transaction isolation level 1: READ UNCOMMITTED
   • set hibernate property

     <property name="hibernate.connection.isolation">1</property>

   • setting transaction isolation level 1: READ UNCOMMITTED will ONLY work for the MySQL, MSSQL, for

       
       1: READ UNCOMMITTED
       2: READ COMMITTED
       4: REPEATABLE READ
       8: SERIALIZABLE
     

   • Support status for Transaction Isolation Levels

     Sr. #	Database Engine	Support Status	Reference Doc	Comment
     1	MySQL	YES	doc url	Fully supported
     2	MS SQL	YES	doc url	Fully supported
     3	Oracle	NO	doc url	Not supported
     4	PostgreSQL	YES	doc url	Level 1 is internally set to level 2