Thread: Lock table, best option?

Hi,

I need to do a SELECT and an UPDATE, but I will have concurrent processes doing the same task.

How can I prevent that the concurrent task don't have the same results in the SELECT? Locking a table? How can I do that?

Best Regards,

On Sat, Apr 24, 2010 at 4:46 PM, Andre Lopes <lopes80andre@gmail.com> wrote:
> I need to do a SELECT and an UPDATE, but I will have concurrent processes
> doing the same task.
>
> How can I prevent that the concurrent task don't have the same results in
> the SELECT? Locking a table? How can I do that?

It sounds like you might be looking for SELECT ... FOR UPDATE, see:
http://www.postgresql.org/docs/current/static/sql-select.html

Basically, you could have each transaction issue SELECT ... FOR UPDATE
for rows intended to be updated later. Only one transaction would be
able to acquire the necessary locks for the same rows at the same
time; the other transaction(s) would block until the locks are
released by the first transaction's commit, and then would see the new
values. This paragraph assumes you're using the default "read
committed" transaction isolation level, you might want to read more
at:
http://www.postgresql.org/docs/current/static/transaction-iso.html

And if you really want to know about full table locking, you can read more at:
http://www.postgresql.org/docs/current/static/sql-lock.html

though it doesn't sound like you'll actually need full table locks.

Josh

On Sat, Apr 24, 2010 at 2:46 PM, Andre Lopes <lopes80andre@gmail.com> wrote:
> Hi,
>
> I need to do a SELECT and an UPDATE, but I will have concurrent processes
> doing the same task.

If you're selecting and updating the same rows, then select ... for
update is preferred and adequate.  If you're selecting one set of rows
and updating another set / another table, then you may have to lock
the tables concerned.

> How can I prevent that the concurrent task don't have the same results in
> the SELECT? Locking a table? How can I do that?

Lock table locks a table.  But if select ... for update will work then
that is preferred.