mod_perl: locking and sharing between sessions

johnnywang has asked for the wisdom of the Perl Monks concerning the following question:

Hi, please excuse me for the title, I'm seeking some ideas as to how to do the following. I'm running a mod_perl script in linux/apach2. When a client accesses the script, it reads from a large mysql table, and hands out a row from that table. I'd like it so that different clients get different records. What I have done is to have a column in the table to mark it as "used" whenever it hands out. Of course, the problem is that when many clients are accessing it at about the same time, there is a racing condition. What is the best way to do this? I'd like this to be efficient. I can create a lock on a file and force sequential access, but not sure how efficient that is. Also the table is pretty large, I'd like to avoid querring the table each time a client accesses, so I'm thinking of getting a chunk out at a time, say 100 records, but then how do I share them among different sessions? Thanks.

Comment on mod_perl: locking and sharing between sessions

Replies are listed 'Best First'.
Re: mod_perl: locking and sharing between sessions by rdfield (Priest) on Oct 14, 2004 at 08:14 UTC
You could use something like Cache::Cache to cache sequence numbers and use a mod_perl handler to replenish them as and when required. rdfield	[reply]
Re: mod_perl: locking and sharing between sessions by perrin (Chancellor) on Oct 13, 2004 at 22:03 UTC
Your question is kind of all over the place. However, in terms of handing out access to specific rows, that is easy to deal with. You will need to lock the table if you are using MyISAM tables. InnoDB lets you do row-level locking instead. Alternatively you can do an update with a WHERE clause that will make it fail for rows that are already taken, and then check to see if it succeeded or not. I'd suggest storing some kind of timestamp and a user or session ID with your lock, so you can clear out abandoned ones after a certain period of time.	[reply]