This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Help!!! Insert a new record with a field whose value based on existing records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help!!! Insert a new record with a field whose value based on existing records" Watch "Help!!! Insert a new record with a field whose value based on existing records" New topic
Author

Help!!! Insert a new record with a field whose value based on existing records

Tai Tran
Greenhorn

Joined: Oct 18, 2011
Posts: 2
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!!!
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3434
    
  47

I cannot offer direct solution for MySQL, I don't work with it, but a few notes:

1) Will records ever get deleted from this table? If so, you'll get gaps. Is that ok?

2) How often will a new contract be inserted?
- A few every hour? Then forget about performance, just lock the whole table to make sure no collisions will occur. Data integrity is much, much more important than saving two milliseconds of processing every hour.
- Many per minute or even second? Yes, you do need to care about performance.

3) If performance is paramount, I'd do this (based on my Oracle experience): do not create and store sequential number, store the insertion time instead, as precise as possible. The sequential number can be computed from data in the table. In Oracle, I'd use analytics for this, is MySQL, reading the data sorted by signed_date, insert_time, id and assigning number as records are read will certainly do it. Sorting by id is added to define order of two records inserted at the same moment. There is no meaningful way to order such rows anyway, you'd have to serialize access (see point 1) to avoid this situation, and it would still be random which of these two records came first.

If records can be deleted from this table, don't actually delete them, just mark them as deleted. Otherwise the computed sequential numbers would get shifted, which I assume is undesirable.

Hope that helps.
Tai Tran
Greenhorn

Joined: Oct 18, 2011
Posts: 2
Thanks for your blazing quick reply!

I will go with the option of setting transaction level to maximum (SERIALIZABLE) as contracts are not so often inserted into the db.

Thanks again,
Tai

Martin Vajsar wrote:I cannot offer direct solution for MySQL, I don't work with it, but a few notes:

1) Will records ever get deleted from this table? If so, you'll get gaps. Is that ok?

2) How often will a new contract be inserted?
- A few every hour? Then forget about performance, just lock the whole table to make sure no collisions will occur. Data integrity is much, much more important than saving two milliseconds of processing every hour.
- Many per minute or even second? Yes, you do need to care about performance.

3) If performance is paramount, I'd do this (based on my Oracle experience): do not create and store sequential number, store the insertion time instead, as precise as possible. The sequential number can be computed from data in the table. In Oracle, I'd use analytics for this, is MySQL, reading the data sorted by signed_date, insert_time, id and assigning number as records are read will certainly do it. Sorting by id is added to define order of two records inserted at the same moment. There is no meaningful way to order such rows anyway, you'd have to serialize access (see point 1) to avoid this situation, and it would still be random which of these two records came first.

If records can be deleted from this table, don't actually delete them, just mark them as deleted. Otherwise the computed sequential numbers would get shifted, which I assume is undesirable.

Hope that helps.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3434
    
  47

SERIALIZABLE should do it. I would, for the peace of my soul, use exclusive lock on the table, but Oracle is very different from MySQL in this regard. Unless you've got profound understanding of your database, test your approach by simulating your transactions in a generic client (TOAD or whatever similar tool MySQL has).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help!!! Insert a new record with a field whose value based on existing records
 
Similar Threads
Conneting to database but unable to save object.
Problem with the <set> mapping please help
My Study Notes
Conversion help plz!!!
Unable to get hibernate session