In August 2010, I sent an email to pghackers reporting a strange deadlock problem.
I couldn’t understand how it was possible you could get a deadlock by updating
the very same row again later in the same transaction.
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg157869.html
I also made a screencast demonstrating the problem:
http://www.screencast.com/users/joeljacobson/folders/Jing/media/42c31028-80fa-45fe-b21f-9039110c3555
Simon Riggs came up with a very clever solution to this problem.
By introducing a new type of lock, it would be possible to avoid a lot of deadlocks.
Álvaro Herrera has since then been working on the implementation.
It turned out to be a lot of work, unfortunately the feature didn’t make it into 9.2.
I bet this will be one of the most important performance features in 9.3.
It will greatly improve the performance by increasing the concurrency possible if you have a high transaction load.
I thought it would be fun to test my old example from 2010 using the latest version of the patch.
Let’s checkout Álvaro’s latest version of the fklocks patch:
git remote add fklocks git://github.com/alvherre/postgres.git
git fetch fklocks
git checkout fklocks
CREATE TABLE A ( AID integer not null, Col1 integer, PRIMARY KEY (AID) ); CREATE TABLE B ( BID integer not null, AID integer not null, Col2 integer, PRIMARY KEY (BID), FOREIGN KEY (AID) REFERENCES A(AID) ); INSERT INTO A (AID) VALUES (1); INSERT INTO B (BID,AID) VALUES (2,1); -- Process 1: BEGIN; -- Process 2: BEGIN; UPDATE A SET Col1 = 1 WHERE AID = 1; UPDATE B SET Col2 = 2 WHERE BID = 2; UPDATE B SET Col2 = 3 WHERE BID = 2; -- Process 1 is waiting UPDATE B SET Col2 = 4 WHERE BID = 2; -- Process 2 is allowed to update the same row COMMIT; COMMIT; SELECT * FROM A; aid | col1 -----+------ 1 | 1 (1 row) SELECT * FROM B; bid | aid | col2 -----+-----+------ 2 | 1 | 3 (1 row)
Both transactions were able to COMMIT successfully, hurray!
Without this patch, you would get a “deadlock detected” on row 30,
when Process 2 tries to update the same row again, BID = 2.
However, I was a bit surprised the final value of Col2 is 3 and not 4,
as Process 2 updated the row BID = 2 before Process 1,
and Process 2 updated the row again after Process 1.
This probably has a logical explanation.
I hope to have time to do some concurrency stress testing using
example code and data extracted from our production database.
Would be interesting to see if my old read-life deadlock scenarios
are fixed by this patch and how it affects concurrency and performance.
Optimistic updates result in the second commit overwriting the first. While the update statement for 4 came later the commit for 4 came before the commit for 3.
Or, to put it a different way: Process 1 blocks when it attempts to update Col2, and cannot progress until process 2 commits. Once process 2 commits, process 1 can continue to run where it left off, updating Col2. It does so, then commits. The update for process 1 actually occurs after the update for process 2, because the update doesn’t occur until process 1 unblocks.
The final value of Col2 being 3 is actually correct. Process 1 blocks until the commit of process 2 when the lock on that row is released.
As J Prevost mentioned, the update in process 1 doesn’t happen till *after* the commit in Process 2.
Anonymous is incorrect: it has nothing to do with the order of the commits. And the “update for 4” *didn’t* come later — but it was *submitted* later.
To get around this “problem” of “lost updates” you’ll need to do either of two things:
1. use a serializable transaction isolation level for both processes,
see http://www.postgresql.org/docs/current/static/transaction-iso.html
2. rewrite your update statements to include the expected “old” value. Or you can rework your data model and add either of these two columns:
A. changed_on timestamp default now()
B. version integer column default 0
and, again, rewrite your update statements to include the expected “old” changed_on or version value. And update the changed_on = now() or version = version + 1.
BUT
Great find, this inability to “reupdate” is something that should be fixed.
I believe everything published made a bunch of sense.
But, think on this, what if you composed a catchier post title?
I am not suggesting your content is not solid, but what if you added
a headline to maybe get a person’s attention? I mean Foreign Key Locks | Joel on SQL is kinda plain. You could glance at Yahoo’s front
page and see how they create news titles to grab people interested.
You might add a video or a related picture or two to grab people excited about
what you’ve written. Just my opinion, it might bring your posts a little bit more interesting.
I would like to express some tahnks to you just for rescuing me from this matter. As a result of scouting through the the web and coming across tricks which were not helpful, I figured my life was done. Being alive without the solutions to the issues you have solved by way of this guide is a critical case, as well as the kind that could have negatively damaged my entire career if I had not discovered the website. Your own training and kindness in dealing with every item was excellent. I’m not sure what I would have done if I hadn’t come across such a thing like this. I’m able to at this moment look forward to my future. Thanks so much for this skilled and result oriented guide. I will not be reluctant to endorse your blog to any person who needs to have recommendations about this issue.
I think you’ve just captured the answer perfectly
I think you’ve just captured the answer perfectly
Tremendous issues here. I’m very happy to look your article.
Thanks so much and I’m taking a look ahead to contact you.
Will yoou please drop me a e-mail?
This is getting a bit more sjebuctive, but I much prefer the Zune Marketplace. The interface is colorful, has more flair, and some cool features like Mixview’ that let you quickly see related albums, songs, or other users related to what you’re listening to. Clicking on one of those will center on that item, and another set of neighbors will come into view, allowing you to navigate around exploring by similar artists, songs, or users. Speaking of users, the Zune Social is also great fun, letting you find others with shared tastes and becoming friends with them. You then can listen to a playlist created based on an amalgamation of what all your friends are listening to, which is also enjoyable. Those concerned with privacy will be relieved to know you can prevent the public from seeing your personal listening habits if you so choose.
That’s cleared my thoughts. Thanks for contributing.
Thanks to my father who shared with me about this webpage, this website is actually remarkable.
We are a gaggle of volunteers and starting a new scheme in our community.
Your web site offered us with useful information to work on.
You’ve performed a formidable activity and our entire neighborhood can be grateful to you.
I have to thank you for the efforts you’ve put in writing this blog.
I’m hoping to check out the same high-grade blog posts from
you in the future as well. In fact, your creative writing abilities has motivated me to
get my very own website now 😉
Hi there friends, how is the whole thing, and what you desire to say
concerning this article, in my view its truly amazing in favor of
me.
It appears as though one page is completed by you, then are taken up tto another page to perform, and then another, and so forth.
There’s aanything aattractive in the comfort of your household gifing your belief about working.
It pays tο throw ʏоur financial web online, աɦere opportunities abound.
Monetizing ʏⲟur knowledge — աhether іn transcribing
or socialnetworking — іѕ one waү tⲟ generate profits ԝhile reating аt yolur PC in thе hοme.
What’s up, its pleasant article regarding media print, we all be familiar with media is a great source of data.