On Fri, Oct 19, 2012 at 1:22 AM, Kevin Grittner <kgrittn@xxxxxxxx> wrote: > Now, if no records are inserted or deleted by another connection, how > many rows will be deleted by this statement?: > > delete from rc where id = (select min(id) from rc); > > It's a trick question; the answer depends on a race condition. > > Before you run that delete, on a separate connection run this: > > begin; > update rc set id = id - 1; > > Run your delete on the first connection and it will block. Commit > the transaction on the second connection, and go look at the results > on the first, and select from the table. Ah yes, I believe that one can be best explained with the Magic: The Gathering rules about triggers and the "intervening if" clause. In M:TG, a trigger might say "At the beginning of your upkeep, if you have 40 or more life, you win the game". It checks the condition twice, once when it triggers, and again when it resolves. (Yeah, I'm a nerd. I know detaily rules to a nerd's game. So sue me.) In the first pass, Postgres decides which row(s) should be deleted. Okay, let's find the one that's the lowest ID. Lowest ID is 1, that means this record. Hey, mind if I delete this? Oh, it's locked. Hold on. Commit the other one. Okay, let's go delete this one. Check to see that it still matches the WHERE clause. Does it have ID=1? Nope, ID=0. Don't delete anything! Yes, it's slightly odd. But really, if you're doing sweeping changes like that, a table-level massively exclusive lock is probably the best way to do it. I haven't seen any issues with READ COMMITTED that have caused problems; although that's partly because I usually have an immutable ID 'serial primary key' on every row. Helps a lot. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
