tag:blogger.com,1999:blog-2341502915119631850.post2958807689212097729..comments2023-12-12T17:47:03.291+01:00Comments on The Puf Principle: Optimistic locking vs. pessimistic lockingFrankhttp://www.blogger.com/profile/13688437747795053947noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-2341502915119631850.post-53515124660690564342016-04-08T06:45:46.669+01:002016-04-08T06:45:46.669+01:00
Nice Article !
Really this will really help to p...<br />Nice Article !<br /><br />Really this will really help to people of Database Community. <br />I have also prepared small note on this, What is Optimistic locking and Pessimistic locking.<br /><br />http://www.dbrnd.com/2016/04/database-theory-what-is-optimistic-locking-and-pessimistic-locking/Anonymoushttps://www.blogger.com/profile/01365030762397385247noreply@blogger.comtag:blogger.com,1999:blog-2341502915119631850.post-50931688180197058772010-07-04T05:07:20.040+01:002010-07-04T05:07:20.040+01:00Hi Frank, I follow you and agree with you now. Tha...Hi Frank, I follow you and agree with you now. Thanks for replying promptly. And btw, did I forget to mention that I found this post of yours overall quite a lucid read?!<br /><br />Regards,<br />/HarryAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2341502915119631850.post-63076408278494769262010-07-03T16:17:57.326+01:002010-07-03T16:17:57.326+01:00Hi Harry,
Imagine a table with four columns: ID, ...Hi Harry,<br /><br />Imagine a table with four columns: ID, version, count, day. Let's also assume there is no relation whatsoever between these columns. We have a row with these values:<br /><br />42,1,100,Monday<br /><br />And we have two updates that we want to execute:<br /><br />subtract 50 from count<br />set day to Saturday<br /><br />If we write these with version based locking, we end up with:<br /><br />UPDATE table<br />SET count = 50<br />WHERE id=42 AND version=1<br /><br />UPDATE table<br />SET day = "Saturday"<br />WHERE id=42 AND version=1<br /><br />There is no way these two updates could ever execute in parallel. One of them will fail and the application logic will have to read back the new version number, resolve potential conflicts (none in this case) and retry.<br /><br />When using value-based locking, you'll end up with queries like these:<br /><br />UPDATE table<br />SET count = 50<br />WHERE id=42 AND count=100<br /><br />UPDATE table<br />SET day = "Saturday"<br />WHERE id=42 AND day="Monday"<br /><br />These two queries can execute in parallel.<br /><br />This is a very simplistic and contrived example and there are many cases where this simply will not work. In those cases locking on multiple columns or introducing a version column to lock the entire row are totally fine. <br /><br />But in cases where a value-based lock can suffice, you'll end up decreasing (potential) throughput if you use a version-based lock.<br /><br />Thanks for your feedback.<br /><br /> FrankFrankhttps://www.blogger.com/profile/13688437747795053947noreply@blogger.comtag:blogger.com,1999:blog-2341502915119631850.post-61231255328379401332010-07-03T09:38:23.859+01:002010-07-03T09:38:23.859+01:00@Frank Although a version column is a simple way t...@Frank Although a version column is a simple way to ensure that no conflicting updates occur, it is (just like pessimistic locking) also a sub-optimal one. Essentially it ensures that no concurrent updates occur, whether they are conflicting or not.<br /><br />I'm not sure I understood why the version column-based solution is sub-optimal. Would you please elaborate...? as I think (1) that the version-column-based solution would be just as efficient as checking the old value of stock_level or comparing timestamps, and (2) that some tables may not have even one 'efficient' column type (and thus value) to use for comparisons (let's say, all columns in a table are all string type)... forcing you to either use timestamp column approach or the version number approach.<br /><br />I'm assuming that when implementing a version-column-based solution, one would use an auto-incrementing integer type column... so that it too, just like the timestamp column, does not require 'user code' to manually increment the current version and thus start being inefficient relative to timestamp approach where the timestamp update would happen inside the more efficient 'system code'.<br /><br />/HarryAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2341502915119631850.post-18009310397919135132009-04-12T16:06:00.000+01:002009-04-12T16:06:00.000+01:00The question of whether to use of a version column...The question of whether to use of a version column is actually the main reason I wrote this post. I noticed the use of a version column in some code I was reading and wondered why the author introduced such a column.<BR/><BR/>Although a version column is a simple way to ensure that no conflicting updates occur, it is (just like pessimistic locking) also a sub-optimal one. Essentially it ensures that no concurrent updates occur, whether they are conflicting or not.<BR/><BR/>Depending on the database schema there are many types of concurrent updates that can happen without losing data integrity. They could be updating different columns that don't have dependencies on each other.<BR/><BR/>I started writing a post about exactly the use of version number columns vs. the use of field values in optimistic locking applications, but somehow ended up with this post instead. Your feedback shows that I really should finish the other post as well.Frankhttps://www.blogger.com/profile/13688437747795053947noreply@blogger.comtag:blogger.com,1999:blog-2341502915119631850.post-27958839502396754482009-04-12T08:28:00.000+01:002009-04-12T08:28:00.000+01:00or add a new column called lock_version starts at ...or add a new column called lock_version starts at zero when the record is inserted. Select the lock_version when you read from the table then compare lock_version with the value selected when you update the table, in the update also increment lock_version by 1. <BR/><BR/>SELECT lock_version, stock_level WHERE id = ?<BR/><BR/>UPDATE lock_version = lock_version + 1, stock_level = ? WHERE id = ? and lock_version = lock_version<BR/><BR/>Some databases (Sybase and MS-SQL for example) have a timestamp data type that is updated automatically and have a special function (tsequal?) that will throw an error which you can recognize as an optimistic update problem. Now you can update any column on the table without getting into trouble. <BR/><BR/>SELECT timestamp, stock_level WHERE id = ?<BR/><BR/>UPDATE stock_level = stock_level WHERE id = ? AND tsequal(timestamp, old_timestamp)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2341502915119631850.post-67650294735897287082009-04-12T07:55:00.000+01:002009-04-12T07:55:00.000+01:00You should look into Transactional Memory. I'm wri...You should look into Transactional Memory. I'm writing a paper on it for my CS grad classes right now...all the security and data integrity of lock-based programming, without...well, the locking aspect. <BR/><BR/>No deadlocks. Improved performance. Bliss.Adam Conradhttps://www.blogger.com/profile/11340276497150565217noreply@blogger.com