concurrency
-
Hello,
we have about 100 users that need to pickup up a record (select ...) from many millions in a single table, take some decision, type additional data to the record picked up before and then send it to the database (update ...)
the problem is that am having problems cause some users are picking up records already taken by other users, how can i take the available record give it to the user (locked exclusively??) until he updates it and then release it.
thanks
Gilberto from DR
-
A simple way of doing it would be to have a checked_out column with a bit value, set this bit as 1 any time that the record is out for updating and set it back to 0 afterwards. Then any proc trying to pull records from the table would check the bit value and either prevent retreival of the record or allow it to be pulled. You could also set a date matching the checked column and run a job every so often to prevent permanently locked records by setting the bit back to 0 for any record checked out for more than x minutes.
-
The "update flag" method described above works with limited table sizes
the bigger the table the higher the contention, the slower the process and you are back to square 1
One solution I have found to be fairly efficient is to use a separated table, Insert the primary key in and if the insertion fails it means that someone else is using the record .
(the primary key of that table is composed by tablename and pkvalue
)It is very fast because the contention activity is reduced to a small set of records instead.
there are other methods like timestamp columns and guid columns but each has its pros and cons. I like the one I just described to you due to the fact that can be easily implemented across platforms and the table model remains in synch with the physical implementation. But feel free to use the one that your environment is more comfortable with
* Noel
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply