GeeCON Prague 2014*
The moose likes JDBC and the fly likes Threading and Unique Record Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Threading and Unique Record Question" Watch "Threading and Unique Record Question" New topic
Author

Threading and Unique Record Question

Scott Maclary
Ranch Hand

Joined: May 11, 2005
Posts: 34
I have a struts action "CreateUserAction" which calls a DAO method "createUser" method which contains two SQL statements:

SELECT * FROM USERS WHERE USER_NAME=?

(if it does not exist)

INSERT INTO USERS ....

My question is, couldn't the DAO method "createUser" thread stop (sleep whatever) right after the "SELECT * FROM USERS WHERE USER_NAME=?" for a brief moment before running the "INSERT INTO USERS ...." SQL executes...meaning that another user could come along and register with the same USER_NAME before the "INSERT INTO USERS ...." runs?

This would result in duplicate users with the same USER_NAME.

Is my threading assumption correct?


PS - I can't put a database UNIQUE constraint on USERS.USER_NAME.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Sure, it could. In most real-world systems, people assume that situations where two clients are going to attempt to register the same user at exactly the same instant will be extremely rare and (therefore) not worth worrying about.
Scott Maclary
Ranch Hand

Joined: May 11, 2005
Posts: 34
Sure, it could. In most real-world systems, people assume that situations where two clients are going to attempt to register the same user at exactly the same instant will be extremely rare and (therefore) not worth worrying about.


This "should" be extremely rare...except we are seeing it on our system with 5 million users. The USERS.TIMESTAMP is always the same so my guess is that their browser goes into some crazy state making the same request over and over again within an instant.

If I moved the two statements into a stored procedure - would this help?
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
You really have three potential choices:

- Create a dedicated set of connections for new-user entry, and set the isolation level of these connections as serializable.
- Move things to a stored procedure like you said, PROVIDED you can set the isolation level of the stored proc indepedently and as serializable.
- Create a new USERNAME table where the USER_NAME is a primary key and make it a part of your username entry code. Thus, the entire transaction will fail, things will rollback, and you can display a nice error to the user.
Dawn Charangat
Ranch Hand

Joined: Apr 26, 2007
Posts: 249
Why dont you put both the sql statements in one single "synchronized" block ?

In that case, even if another user comes in, he will have to wait until the already existing one in the block has to move out. I know it is a performance overhead, but I suppose it would solve your problem.
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
Actually, the DBMS probably won't allow mixed isolation levels :P
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
Mmm...The synchronized approach could work.

Honest question: What happens in a clustered scenario, if the entire session is serializable? Will app server respect the object lock?
Dawn Charangat
Ranch Hand

Joined: Apr 26, 2007
Posts: 249
wont it have to ? I mean the spec says that anything in the synchronized block is the critical section, and can be executed by only one thread at a time.... so the appserver [if compliant to J2EE specs]
have to respect that, right - clustered or not ? [thinking aloud]

Even if it doesnt, if we make the DB operation class a singleton, and then apply the synchronization on it, shouldn't it work? [lets throw the performance requirements out of the window for a while ]
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
That's the $1,000,000 question and so I'm not sure

Everybody complains about singletons in this sort of scenario; I've also been out of the app-server loop for a few years so I don't know the current state of things.

Another thought (overkill): Use a messaging service.

Edit: Without really understanding what it is, I found a reference to MBean Singleton?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30584
    
154

Dawn Charangat wrote:wont it have to ? I mean the spec says that anything in the synchronized block is the critical section, and can be executed by only one thread at a time.... so the appserver [if compliant to J2EE specs]
have to respect that, right - clustered or not ? [thinking aloud]

Even if it doesnt, if we make the DB operation class a singleton, and then apply the synchronization on it, shouldn't it work? [lets throw the performance requirements out of the window for a while ]

I don't think this will work. The synchronized block or singleton is meant for the same JVM. If you are clustered, you will have two singletons which defeats the purpose.

A transaction (or stored procedure which gives you a transaction) is pretty much the only guarantee.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
I can't believe I didn't think of this earlier and you probably won't read this, but the real answer here is a trigger.

- Create the new table, with the username/userid keyed.
- Add insert/delete triggers on your original table.

This is the proper design: This does what you want, no code change is required on the Java side, and it works regardless of WHICH application inserts into your user table.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

John Kimball wrote:I can't believe I didn't think of this earlier and you probably won't read this, but the real answer here is a trigger.

- Create the new table, with the username/userid keyed.
- Add insert/delete triggers on your original table.

This is the proper design: This does what you want, no code change is required on the Java side, and it works regardless of WHICH application inserts into your user table.


Well, this is really just a work around (though a very sensible one!). As with everything in databases, the "proper" design is to fix the data model so it reflects the business rules you want to enforce. Add a unique constraint to username and catch the constraint violation exception. Why can't you do this?
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
I agree the username should have a unique constraint, but I'm just taking the original poster's word for it that they can't.

I'm actually curious as to why this is, so hopefully the OP reads this and has an interesting answer for us
 
GeeCON Prague 2014
 
subject: Threading and Unique Record Question