Tuesday, December 10, 2013

I understand the differences between optimistic and pessimistic locking*. Now could someone explain to me when I would use either one in general?
And does the answer to this question change depending on whether or not I'm using a stored procedure to perform the query?
*But just to check, optimistic means "don't lock the table while reading" and pessimistic means "lock the table while reading."
share|improve this question
add comment

5 Answers

up vote122down voteaccepted
Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.
This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.
Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.
In the latter case you open the transaction with the TxID and then reconnect using that ID. The DBMS maintains the locks and allows you to pick the session back up through the TxID. This is how distributed transactiona using two-phase commit protocols (such as XA or COM+ Transactions) work.
share|improve this answer
33 
Optimistic locking doesn't necessarily use a version number. Other strategies include using (a) a timestamp or (b) the entire state of the row itself. The latter strategy is ugly but avoids the need for a dedicated version column, in cases where you aren't able to modify the schema. –  Andrew Swan Dec 8 '09 at 22:33
 
The concept of optimistic locking doesn't necessarily require having a 100% reliable way of knowing whether or not something has been altered; undetectable alterations aren't acceptable, but occasional false reports of alteration may not be too bad, especially if code which receives such a report rereads the data and checks whether it has actually changed. –  supercat Jul 8 at 21:43
add comment
Optimistic locking is used when you don't expect many collisions. It costs less to do a normal operation but if the collision DOES occur you would pay a higher price to resolve it as the transaction is aborted.
Pessimistic locking is used when a collision is anticipated. The transactions which would violate synchronization are simply blocked.
To select proper locking mechanism you have to estimate the amount of reads and writes and plan accordingly
share|improve this answer
add comment
Optimistic assumes that nothing's going to change while you're reading it.
Pessimistic assumes that something will and so locks it.
If it's not essential that the data is perfectly read use optimistic. You might get the odd 'dirty' read - but it's far less likely to result in deadlocks and the like.
Most web applications are fine with dirty reads - on the rare occasion the data doesn't exactly tally the next reload does.
For exact data operations (like in banking) use pessimistic. It's essential that the data is accurately read, with no un-shown changes - the extra locking overhead is worth it.
Oh, and Microsoft SQL server defaults to page locking - basically the row you're reading and a few either side. Row locking is more accurate but much slower. It's often worth setting your transactions to read-committed or no-lock to avoid deadlocks while reading.
share|improve this answer
 
JPA Optimistic Locking allows you to guarantee read-consistency. –  Gili Sep 24 '08 at 19:43
add comment
In addition to what's been said already, it should be said that optimistic locking tends to improve concurrency at the expense of predictability. Pessimistic locking tends to reduce concurrency, but is more predictable.
You pays your money, etc

5 comments:

oakleyses said...

louis vuitton handbags, oakley sunglasses, louboutin, longchamp outlet, nike shoes, louis vuitton outlet stores, chanel handbags, burberry outlet, prada outlet, jordan shoes, tiffany and co, michael kors outlet, tory burch outlet, louis vuitton outlet, longchamp handbags, nike free, true religion jeans, michael kors outlet, kate spade outlet, polo ralph lauren outlet, tiffany and co, prada handbags, polo ralph lauren outlet, michael kors outlet, michael kors outlet, longchamp handbags, oakley sunglasses, ray ban sunglasses, kate spade handbags, burberry outlet, louis vuitton outlet, louboutin outlet, louboutin, coach factory outlet, air max, air max, coach outlet, gucci outlet, christian louboutin shoes, michael kors outlet, coach purses, ray ban sunglasses, michael kors outlet, louis vuitton, coach outlet store online, true religion jeans, oakley sunglasses cheap

oakleyses said...

ralph lauren, lululemon, air max, hollister, north face, nike air max, polo lacoste, vanessa bruno, timberland, vans pas cher, louboutin, louis vuitton, oakley pas cher, air max pas cher, nike roshe run, air max, true religion outlet, barbour, sac longchamp, air force, hollister, sac louis vuitton, nike free, polo ralph lauren, nike trainers, louis vuitton uk, nike roshe, sac hermes, longchamp, michael kors, sac burberry, sac guess, mulberry, new balance pas cher, converse pas cher, sac louis vuitton, hogan outlet, nike tn, north face, true religion outlet, ray ban pas cher, michael kors, air jordan, nike blazer, nike free pas cher, michael kors pas cher, abercrombie and fitch, ray ban sunglasses

oakleyses said...

mac cosmetics, mont blanc, marc jacobs, canada goose outlet, nike huarache, vans shoes, soccer jerseys, hollister, giuseppe zanotti, beats by dre, abercrombie and fitch, longchamp, insanity workout, celine handbags, bottega veneta, ghd, nfl jerseys, north face outlet, chi flat iron, ugg boots, birkin bag, ugg australia, canada goose, herve leger, ugg pas cher, rolex watches, valentino shoes, canada goose uk, canada goose, ferragamo shoes, canada goose, ugg boots, uggs outlet, north face jackets, soccer shoes, asics running shoes, new balance shoes, p90x, lululemon outlet, canada goose jackets, mcm handbags, instyler, babyliss pro, ugg, wedding dresses, jimmy choo outlet, reebok outlet, nike roshe run

oakleyses said...

parajumpers, karen millen, air max, converse, pandora charms, moncler, louboutin, moncler, links of london, lancel, juicy couture outlet, oakley, hollister, pandora charms, supra shoes, thomas sabo, canada goose, gucci, wedding dresses, timberland boots, swarovski crystal, air max, coach outlet store online, moncler, ray ban, canada goose, moncler, ugg, louis vuitton, swarovski, hollister, montre homme, moncler, hollister clothing store, ralph lauren, rolex watches, moncler outlet, moncler, iphone 6 cases, baseball bats, juicy couture outlet, toms shoes, vans, pandora jewelry, ugg, converse shoes

Zheng junxai5 said...


zhengjx20160721
true religion sale
nike uk
celine bags
michael kors outlet online
oakley sunglasses outlet
adidas boost
oakley outlet
coach outlet canada
beats by dr dre
adidas nmd
kobe 8
nike sb janoski
michael kors outlet
adidas originals
coach factory outlet online
coach factory outlet online
michael kors handbags
nike roshe run
tory burch outlet online
oakley sunglasses
timberlands
coach factory outlet online
coach outlet store online clearances
michael kors outlet
basketball shoes
nike air max uk
tods sale
lebron 12
cheap ray ban sunglasses
coach outlet online
michael kors
jordan retro 11
michael kors outlet clearance
coach outlet store online clearances
air jordan shoes
oakley sunglasses
beats by dre outlet
ray ban sunglasses
oakley outlet
coach outlet clearance