Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC, concurrent access

 
Shreya Menon
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shreya,
They both do different database queries, right? And the database can handle concurrent queries. So what is the problem?
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe, select ... for update might help.
 
Shreya Menon
Ranch Hand
Posts: 285
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic