JDBC, concurrent access

[Shreya Menon]

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]

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

[stu derby]

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]

I believe, select ... for update might help.

[Shreya Menon]

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]

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]

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...