Hi, Previously, we had some discussion to introduce a new type of tuple lock to let foreign keys be lighter weight, allowing for more concurrency. During the course of that discussion, it became evident that the solution being proposed didn't go all the way to solve the problem. A proposal by Noah Misch seems like it would fix the whole problem, but it requires some more rejiggering than I had considered.
This email summarizes what I just posted in a blog article here: http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/ and adds some implementation details. The proposal there is to create two new tuple lock types, not one. They have been called "KEY UPDATE" and "KEY SHARE". Proposed conflict table: KEY UPDATE FOR UPDATE FOR SHARE KEY SHARE KEY UPDATE X X X X FOR UPDATE X X X FOR SHARE X X KEY SHARE X DELETE always grabs KEY UPDATE lock on a tuple. UPDATE grabs KEY UPDATE if the key is being modified, otherwise FOR UPDATE. SELECT FOR UPDATE grabs FOR UPDATE. SELECT FOR SHARE grabs FOR SHARE. SELECT FOR KEY SHARE grabs FOR KEY SHARE. This is the mode used by RI triggers. Note that this means that UPDATE would always have to check whether key columns are being modified. (I loosely talk about "key columns" here referring to columns involving unique indexes. On tables with no unique indexes, I think this would have to mean all columns, but I haven't thought much about this bit.) Note that the KEY UPDATE lock would be an internal option, not exposed to SQL. I think we already have enough extensions in this area. We are forced to expose KEY SHARE because RI triggers get to it via SPI, but I would be happy to avoid doing it if I knew how. I would also love to get rid of FOR SHARE because I don't think they serve any purpose anymore, but since it has already been exposed to SQL for several releases, I'm not sure that it's a viable thing to do. To implement this, we need to augment MultiXact to store the lock type that each comprising Xid holds on the tuple. Two bits per Xid are needed. My thinking is that we could simply extend the "members" to add a byte for every four members. This should be relatively simple, though this forecloses the option of using MultiXact for some other purpose than locking tuples. This being purely theoretical, I don't have a problem with that. Note that the original keylocks patch I posted a couple of weeks ago has a caveat: if transaction A grabs share lock and transaction B grabs key lock, there's no way to know who owns which. I dismissed that problem as unimportant (and probably infrequent); the good thing about this new idea is that we wouldn't have that problem. This would also help us find a solution to the problem that an aborted subtransaction that updates or excl-locks a tuple causes an earlier shared lock to be forgotten. We would deal with this by marking the Xmax with a new MultiXact that includes both the lock and the update. This means that this MultiXact would have to be WAL-logged. I would leave that for a later patch, but I think it's good that there's a way to fix it. Thoughts? -- Álvaro Herrera <alvhe...@alvh.no-ip.org> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
