aspose file tools*
The moose likes JDBC and the fly likes JDBC, concurrent access Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC, concurrent access" Watch "JDBC, concurrent access" New topic
Author

JDBC, concurrent access

Shreya Menon
Ranch Hand

Joined: Jul 31, 2001
Posts: 285
Hi all,

I am having a situation like this:
Say client A and client B trying to do a select from table A, I donnot want to display the same results to both the clients. How do I accomplish this ? Can anyone give me a sample ?

ie, Table A has 10 records and at a time application only displays 5 records. So for two concurrent users, User A should see the first 5 and User B should see the next 5. How can this be done ?

Thanks
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30960
    
158

Shreya,
They both do different database queries, right? And the database can handle concurrent queries. So what is the problem?


[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
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Jeanne Boyarsky:
Shreya,
They both do different database queries, right? And the database can handle concurrent queries. So what is the problem?


The user has explained (in the Sun forum) that the business wants to uniquely offer an item for sale; a sub-set of all items is offered to one person and a different sub-set may concurrently be offered to another.

http://forum.java.sun.com/thread.jspa?threadID=730719&tstart=0
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
I believe, select ... for update might help.
Shreya Menon
Ranch Hand

Joined: Jul 31, 2001
Posts: 285
Jeanne,

Basically some kind of LOCK is needed.
For client A, the first few records need to be displayed. Now these should be locked for displaying for the subsequent users.

How can I accomplish LOCK using JDBC/Oracle ?

I have heard SELECT.. FOR UPDATE and transactions.
But can anyone give me a sample please ?

Thanks
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30960
    
158

I don't think SELECT FOR UPDATE will work here. You only get the update lock until the end of a transaction. While the user is looking at the web page, there isn't a lock. Otherwise, the user could go away and leave your data locked forever.

Another alternative is to add a column to the table with the time that you showed the sale item to a user. If there is a timestamp in this field, don't show it to the next user. (The timestamp is so you can periodically clean up the database with users who abandoned the page.)
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Jeanne Boyarsky:
I don't think SELECT FOR UPDATE will work here. You only get the update lock until the end of a transaction. While the user is looking at the web page, there isn't a lock. Otherwise, the user could go away and leave your data locked forever.

Another alternative is to add a column to the table with the time that you showed the sale item to a user. If there is a timestamp in this field, don't show it to the next user. (The timestamp is so you can periodically clean up the database with users who abandoned the page.)


As mentioned over on the Sun forum where the same question was posted, both an additional column and the use of either SERIALIZABLE transactions or SELECT FOR UPDATE are required, otherwise a concurrent SELECT on a READ_COMMITTED transaction can hand out the same rows to 2 or more users.

The OP then asked for code, which I don't have time to supply...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC, concurrent access