> Anyone know if it is possible to have a row locked automatically when
> you fetch it (through perl DBI)? I have a multi threaded server making
> simultaneous selects and updates to a table, and I am afraid I eill read
> and modify a row which has been modified since I read it, causing a
> bogus value. I cannot lock up the whole table as then multithreading is
> almost useless. Also, I am wary of any lock procedure (even the ones
> supplied) as they are not foolproof unless they use a CPU supplied one
> cycle semaphore or disable interrupts during a multi clock cycle
> semaphore. So, does mysql provide such a function or should I look at
> external (CPU intensive) ways of locking?
> Many thanks
Can you provide an example? I am not quite understanding what you are
doing that may cause a conflict.
Here is my understanding of what you are doing:
- read a row into the memory
- play with it
- write it back to the database
If that is the case, you would have to implement your own locking
mechanism or better, see if you can get rid of the need to do that - do
an atomic update, eg
update foo set n = n + 1;
or if you have to do a join to update the values
replace into foo select ...
each update query is guaranteed to be atomic.