read-test-insert, keep field unique

[Kovacs Antal]

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!

[Ireneusz Kordal]

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.

[Jeanne Boyarsky]

Kovacs,
Welcome to JavaRanch!

Yes. A transaction will help ensure a record isn't added between the check and addition.

[Paul Sturrock]

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]

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]

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]

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]

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?