Rafa Couto wrote:
2005/5/20, Andrew Hammond <[EMAIL PROTECTED]>:
The solution to your problem is locking (or concurrency control if you
prefer). While we're at it, we might as well optimize your statement a
little too using ORDER BY with LIMIT instead of min().
SELECT id INTO _contacto_id
FROM contactos
WHERE contactos.operadora_id IS NULL
AND contactos.actividad_id > = _actividad_id
ORDER BY id LIMIT 1
FOR UPDATE;
Take a look at the "FOR UPDATE" section of the SELECT description for an
explanation of how this works.
I understand "FOR UPDATE" clause is locking while is selecting rows
only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in
next statement. Is not it?
After the SELECT FOR UPDATE command locked some rows, other concurrent
changes to the database could be made, but changes, which require to
lock that rows will be deferred.
The lock will be hold until the end of the transaction (that means at
least until the function returns).
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
