• 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
  • Tim Cooke
  • paul wheaton
  • Ron McLeod
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

read-test-insert, keep field unique

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!

Let's suppose a database table containing (ID, username, detail1, detail2, status ('d'eleted, 'h'istory, 'a'ctual).
I have a method, that selects the rows that are status='a'ctual and username=desired_new_username. If
the result set is empty, then I insert the new record, else say choose a different username. How can I make
sure, that this works well in multi-threaded environment?

case 1: my single application is hitting the database: is the best approach to make all methods inserting into
this table synchronized with the same lock?

case 2: different, unknown applications are also inserting users into this table. how can I make sure, that
no one can write a new record into that table after my SELECT and before my INSERT?

can this be done by choosing a right transaction isolation level?

thanks for anyone, who answers me!
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Kovacs Antal wrote:Hi!
case 2: different, unknown applications are also inserting users into this table. how can I make sure, that
no one can write a new record into that table after my SELECT and before my INSERT?


Create a primary key on a table (or unique constraint) that consist of username+status fields,
and in your program simply try to insert record, then check if a database raise an exception 'duplicate key/index values'.
If yes, the user must enter a different username.
If no - the record is inserted.
 
author & internet detective
Posts: 42173
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Kovacs,
Welcome to JavaRanch!

Yes. A transaction will help ensure a record isn't added between the check and addition.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is that right Jeanne? How does a transaction prevent an insert statement? It will (depending on the isolation level) prevent updates to the selected data, but it surely can only block against existign data?
 
Jeanne Boyarsky
author & internet detective
Posts: 42173
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul,
So I wrote up an explanation of why I was right, got to the end, and wasn't anymore. You are correct. A transaction protects against updates.
 
Kovacs Antal
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!

I'm sorry for not writing so far, but I had no time to react. Thanks for all your answers guys.
Ireneusz Kordal, you said, that "Create a primary key on a table (or unique constraint) that consist of username+status fields"
My question is that how to do it, if I have many (name=user1, status="(H)istory") records and only one (name=user1, status="(A)ctive) ones.
Can I limit the number of records with status = 'A' and name=something to 1?
 
Jeanne Boyarsky
author & internet detective
Posts: 42173
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Kovacs Antal wrote:Ireneusz Kordal, you said, that "Create a primary key on a table (or unique constraint) that consist of username+status fields"
My question is that how to do it, if I have many (name=user1, status="(H)istory") records and only one (name=user1, status="(A)ctive) ones.
Can I limit the number of records with status = 'A' and name=something to 1?


No. You might consider refactoring your database design to have History and Active in different tables. You can still use a view to give the appearance of one table for querying.
 
Kovacs Antal
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!

I think using two separate tables for storing active/deleted/history records is unnecessary, as they contain the very same fields. (Partitioning the table into an active-nonactive part is a handy solution for archiving/performance issues.) Do you think it would be possible with JPA/Hibernate to store some entities in the history table and some in the active table depending on their status?
 
reply
    Bookmark Topic Watch Topic
  • New Topic