• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!!!
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
That new kid is a freak. Show him this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic