Hi pals,
I have a CONTRACT table with 2 interested columns: NO (order number) and SIGNED_DATE, none of which is primary nor foreign key. The problem is that NO column must be auto-incremented on the same signed date. In other words, contracts with the same signed date should have order number increase in order from 1, depending on which one is created first. For example, if the user creates a contract with input signed date is 01/20/2011, then the first one should have No 1 and the one created after that should have No 2, and so on. If the signed date is different then you must start it over again from 1. At the end of day, the CONTRACT table would look like as follows:
At first, I think it is straight forward to get the maximum NO value from all existing records which has the same signed date with the one being created, then add 1 to it for the NO value of newly created contract. However, there could be another contract with the same signed date added to db in between so you would end up with 2 contracts with the same NO value on the same signed date. I use Spring framework for all
JDBC and transactional stuff, creating a new contract is wrapped in a transaction (at default level, READ_UNCOMMITTED?). My db is MySQL 5.5.
Is there any solution to deal with this? Can I set transaction level to maximum (SERIALIZED) to prevent any concurrent update in between in
exchange of some performance degradation?
Thanks in advance for any help!!!