How to force commit Spring - hibernate transaction safely

Question

We are using spring and hibernate for an web application: The application has a shopping cart where user can place items in it. in order to hold the items to be viewed between different login's the item values in the shopping cart are stored in tables. when submitting the shopping cart the items will be saved into different table were we need to generate the order number.

When we insert the values into the table to get the order number, we use to get the max order number and add +1 to it. we are using spring transaction manager and hibernate, in the code flow we get the order number and update the hibernate object to hold the order num value. when i debug, i noticed that only when the complete transaction is issued the order number entity bean is being inserted.

Issue here is when we two request is being submitted to the server at the same time, the same order number is being used, and only one request data is getting inserted. could not insert the other request value which is again a unique one. The order num in the table is a unique one.

i noticed when debugging the persistant layer is not getting inserted into the database even after issuing session flush session.flush()

its just updating the memory and inserting the data to db only at the end of the spring transaction . i tried explicitly issuing a commit to transaction

session.getTransaction().commit();

this inserted the values into the database immediately, but on further code flow displayed message that could not start transaction.

Any help is highly appreciated.

Added: Oracle database i used. There is a sequence number which is unique for that table and also the order number maps to it.

Solution 1

Your first problem is that of serial access around the number generation when multiple thread are executing the same logic. If you could use Oracle sequences this would have been automatically taken care of at the database level as the sequences are guranteed to return unique values any number of times they are called. However since this needs to be now managed at server side, you would need to use synchronization mechanism around your number generation logic ( select max and increment by one) across the transaction boundary. You can make the Service method synchronized ( your service class would be singleton and Spring managed) and declare the transaction boundary around it. However please note that this would be have performance implications and is usually bad for scalability.

Another option could be variation of this - store the id to be allocated in a seperate table with one column "currentVal" and use pessimistic lock for getting the next number. This way, the main table would not have any big lock. This way a lock would be held for the sequence generator code for the time the main entity creation transaction is complete. The main idea behind these techniques is to serialize access to the sequence generator and hold the lock till the main entity transaction commits. Also delay the number generator as late as possible.

The solution suggested by @Vlad is an good one if using triggers is fine in your design.

Regarding your question around the flush behaviour, the SQL is sent to the database at flush call, however the data is not committed until the transaction is committed declaratively or a manual commit is called. The transaction can however see the data it purposes to change but not other transactions depending upon the isolation nature of transaction.

Solution 2

I would set the order number with a trigger which will run in the same transaction with the shopping cart insert one.

After you save the shopping cart, to see the updated order count, you'll have to call:

session.refresh(cart);

The count shouldn't be managed by Hibernate (insertable/updatable = false or @Transient).

Solution 3

follow these steps :- , 1) Create a service method with propagation REQUIRES_NEW in different service class . 2)Move your code (whatever code you want to flush in to db ) in this new method . 3)Call this method from existing api (Because of proxy in spring, we have to call this new service method from different class otherwise REQUIRES_NEW will not work which make sure your flushing data ).

Comments

[Tim]

I used the oracle pessimistic lock approach, because of less design change in the existing code. Thanks, it helped.