2

Hopefully some smarter DBAs than I can help me find a good solution for what I need to do.

For the sake of discussion, lets assume I have a table called 'work' with some number of columns, one of which is a column that represents ownership of that row of work from a given client. The scenario is that I'll have 2 clients connected and polling a table for work to be done, when a row (or some number of rows) shows up, the first client that selects the rows will also update them to imply ownership, that update will remove those rows from being returned to any other client's selects. My question is, in this scenario, what sort of locking can I use to prevent 2 clients from hitting the table at the same time and both of them being returned the same rows via the select?

user483315
  • 83
  • 2
  • 4
  • Will a query such as: update work set owner = clientid where owner is null returning columns operate atomically? eg: 2 clients issuing that query at the same time, they have no chance of manipulating the same rows? – user483315 Apr 12 '11 at 14:49

2 Answers2

10

The UPDATE statement with RETURNING clause is the way to do this.

UPDATE table
SET ownership = owner
RETURNING ( column list );

REFERENCES:

Similar Question

Documentation

Community
  • 1
  • 1
Ketema
  • 6,108
  • 3
  • 21
  • 24
  • In the first link you provided, I'm throwing myself off a little bit I think. That guy is using a transaction block and executing the update inside and then committing it, if I were to do that, while the transaction was executing, my understanding is something could potentially grab the version of the rows before the commit has taken place? If so, executing outside of the transaction should work correctly? – user483315 Apr 12 '11 at 17:52
  • No. The UPDATE statement grabs an exclusive lock on the row(s) that meet its criteria. First come first served. Other transactions would BLOCK if they also met the criteria until the first one finished. If at that point the row(s) no longer met the criteria then the subsequent transactions will get an error and your app has to handle that and try again. However is your criteria is such that multiple rows meet it, but you are limiting the return ala LIMIT, then the subsequent transaction just get the next row that wasn't taken. This is serialization in MVCC world. – Ketema Apr 12 '11 at 22:26
3

My question is, in this scenario, what sort of locking can I use to prevent 2 clients from hitting the table at the same time and both of them being returned the same rows via the select?

No locking needed here.

In the UPDATE, simply specify that you only want the script to take ownership of the task if the owner is still null (assuming that's how you flag unassigned tasks). This should work:

UPDATE foo SET owner = ? WHERE id = ? AND owner = ? WHERE owner IS NULL

If the number of modified rows is equal to the number you expected (or a RETURNING clause returns results as suggested by @Ketema), then you successfully grabbed ownership.

Fake edit because I noticed your comment mere moments before submitting this answer:

eg: 2 clients issuing that query at the same time, they have no chance of manipulating the same rows?

Correct. You might want to read up on MVCC. Running these statements outside of a transaction will do the right thing. Behavior inside a transaction will be different.

Charles
  • 50,943
  • 13
  • 104
  • 142
  • `Behavior inside a transaction will be different.` Could you please elaborate? – pkoch Jan 16 '13 at 14:00
  • 1
    You'll want to read up on [transaction isolation](http://www.postgresql.org/docs/current/static/transaction-iso.html). Depending on the isolation level, you might see things that aren't completely true, and even then, you might encounter a deadlock or conflict and have to deal with that separately. [More on wikipedia](http://en.wikipedia.org/wiki/Isolation_\(database_systems\)). – Charles Jan 16 '13 at 17:25