Tuesday, December 10, 2013

Differences between Pessimistic and Optimistic Locking

Locking is an RDBMS feature that prevents users from different transactions from causing data conflicts. When locking is acquired on a row, it prevents other transactions from changing that row until the transaction ends. In this post I will explain the differences between pessimistic and optimistic locking in the context of ADF framework.
pessimistic locking:
Most Oracle developers are already familiar with pessimistic locking, which was the default locking in BC4J (now optimistic is the  default in 11.1.2.x)). This means that the row is locked in advance once one of  its attribute is changed through a call to setAttribute() method. If anyone else attempts to acquire a lock of the same row during this process, he will be forced to wait until the first transaction has completed. This is achieved by using the familiar SELECT…FOR UPDATE syntax. This is the safest locking mode because two transactions will never make inconsistent change to the same row. However, this locking mode has disadvantages such that:
  1. If a user selects a record for update, and then leaves for lunch without
    finishing or aborting the transaction. All other users that need to update that record are forced to wait until the user returns and completes the transaction, or until the DBA kills the offending transaction and releases the lock.
  2. The Deadlock – Users A and B are both updating the database at the same time. User A locks a record and then attempt to acquire a lock held by user B – who is waiting to obtain a lock held by user A.
Pessimistic locking, which is the default, should not be used for web applications as it creates pending transactional state in the database in the form of row-level locks. If pessimistic locking is set, state management will work, but the locking mode will not perform as expected. Behind the scenes, every time an application module is recycled, a rollback is issued in the JDBC connection. This releases all the locks that pessimistic locking had created.
An example about pessimistic locking based on well known hr schema, suppose user1 and user2 are two different users (two distinct transactions) using pessimistic locking, both of them try to change the same row of data as follows:
  1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, so user1 immediately acquire a lock on that row.
  2. Now user2 calls EmployeesImpl.setSalary(2000) on the same row, user2 tries to acquire a lock on the row and receivesoracle.jbo.AlreadyLockedException.
Optimistic Locking:
Optimistic locking assumes that multiple transactions can complete without affecting each other. Oracle recommends using optimistic locking for web applications. instead of locking a row as soon as it is changed, under optimistic locking, BC4J waits until changed row is posted before attempting to acquire a lock. An exception is not thrown until the conflicting transactions attempt to post their changes to the database.
An example about optimistic locking, suppose user1 and user2 are two different users (two distinct transactions) using optimistic locking, both of them try to change the same row of data as follows:
  1. User1 calls EmployeesImpl.setSalary(1000) on a particular row, user1 does not immediately acquire a lock on that row.
  2. User2 calls EmployeesImpl.setSalary(2000) on  the same row. User1 and User2 now have different entity cache for the same row.
  3. User2 calls commit() action, as part of the commit cycle the changed row is posted to the database. before the update can be executed, user2 acquires a lock on that row. The lock expires immediately, when the commit command is sent to the database.
  4. User1 now calls commit() action, BC4J tries to post the changed row to the database, right before posting it, it attempts to acquire a lock on that row. BC4J recognizes that the row has been changed by another user and that updating the row would overwrite another transaction’s changes, so it throws an oracle.jbo.RowInconsistentException.
Finally, Whatever you use, you can lock a row at any time by calling EntityImpl.lock() on the corresponding entity object instance, even if the locking mode is optimistic.
References:
[1] Oracle 9i Jdeveloper handbook, Peter Koletzeke, Dr. Paul Dorsey, Dr. Avrom Faderman.
[2] Fusion Developer’s Guide for Oracle Application Development Framework
11g Release 1 (11.1.1).

4 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