April 20, 2004 at 10:18 am
Here's my situation: I have a multi-user program that pulls records from a SQL database to work with. Each record needs to be accessed by only 1 user and I am wondering how people have dealt with either marking a record or locking it so that another person can't use it? The application is web based, so I don't think I'll have any way of keeping the record locked, it will need to be marked somehow as it is being selected as the next record. I just need to make sure that the same record isn't sent to two different users at the same time.
Thanks
April 23, 2004 at 8:00 am
This was removed by the editor as SPAM
April 25, 2004 at 4:28 pm
Just a couple of questions before I suggest anything.
Are the users required to log into your app and is their login state persisted in the database? Do you have a way of determining when the user is finished with the record, especially if the user just closes the web browser and walks away? Is it critical that a record not be "locked" for extended periods? Is the locking only required when the records are accessed through the web app? Do you use stored procedures or dynamic sql? Do you persist an application state in the database?
There are many mechanisms that can be used but what is actually implemented will depend on your full concurrency control requirements.
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
April 26, 2004 at 8:59 am
Users won't be logged in at all, and don't really need to lock the record for the entire time they are accessing the info for that record, I just have to make sure no one else uses the record at the same time.
What I have done so far, and in my initial testing it seems to work well, is create a stored proc that looks up the ID of the next record that would be fetched, immediately updates it as having been used so no other users fetch it, and then returns that record. I wrote a little program that would fetch 1000 rows and ran it on 2 different PCs at the same time and neither one ended up with a record in the other's table. I realize that there is still a chance for getting duplicate records using this method, but I think I also have about as good a chance of winning the lottery, so it should work. Any comments or suggestions on this method?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply