Understanding dead locks in Inventory Repository

How locking happens in InventoryManager?
Whenever we call InventoryManager.purchase and InventoryManager.setAvailabilityStatus to update inventory stock level and availability status ATG acquires row-level locking on the inventory item which it is trying to update.

What is row-level locking?
Once a dml query is fired on a row, oracle OOTB will acquire lock on that row and lock on that row will be released once commit or rollback is done on that row. In between acquiring and releasing lock, no other session can acquire lock on same row to update (but still others can read that row). Other transactions (which wants to update the same row) will wait until the lock on that row is released. You can find all locks in v$ object tables like v$lock, v$session ..

Typically, while order is getting placed or once order is placed we need to update inventory.

$class of  InventoryManager OOTB points to atg.commerce.inventory.RepositoryInventoryManager

In atg.commerce.inventory.RepositoryInventoryManager
purchase() {
  //  transaction is required.
  // call getInventoryItemForUpdate
  // call updateItem()
  // end transaction
}

getInventoryItemForUpdate() {
   //  call lock()
   //  get inventory item to update
}

lock(){
    // ensure transaction
    // until InventoryManager.maximumRetriesPerRowLock (default 5)
    // open connection - ((atg.adapter.gsa.GSARepository)getRepository()).getDataSource().getConnection()
    // ps = c.prepareStatement(sql); (sql is InventoryManager.inventoryRowLockSQL - UPDATE dcs_inventory SET inventory_lock = ? WHERE catalog_ref_id = ?)

    // ps.setString(1, lock);  (lock - "inventoryManager")
    // ps.setString(2, pItemId); (pItemId - "skuId")
    // rowcount = ps.executeUpdate();
    // ps.close();
    //        close the connection
    // c.close();
}

Once lock() method is called for one InventoryItem, it will fire udpate sql on that particular row  (the value which it is trying to update is not important here. Which row it is trying to update is important), by default in one transaction if one dml query is fired oracle OOTB acquires lock on that row (you can find those locks in v$objects if you have dba access) and lock on that row is released only once the transaction is either roll backed or committed.


How inventory dead locks happen?
For example
person1 cart has 2 sku's - sku-A and sku-B
person 2 cart has 2 sku's - sku-B and sku-A

If both are trying to place orders at same time.
thread-x of person1 got lock on sku-A inventory item.
thread-y of person 2 got lock on sku-B inventory item.
thread-x waits to get lock on sku-B since thread-y locked it.
thread-y waits to get lock on sku-A since thread-x locked it.
There is dead-lock between thread-x and thread-y

How to avoid those dead locks?
ATG OOTB we have atg.commerce.inventory.RepositoryInventoryManager.acquireInventoryLocks(List pItemIds)
which will sorts the sku's before getting locks on them.
You need to pass list of sku id's which are in the cart to acquireInventoryLocks method.
Once you call acquireInventoryLocks and then call purchase method you can avoid dead locks.

so now

person1 cart has 2 sku's - sku-A and sku-B
person 2 cart has 2 sku's - sku-B and sku-A

If both are trying to place orders at same time.
// first call acquireInventoryLocks - sort and get locks
// second call purchase

thread-x of person1 got lock on sku-A inventory item.
thread-y of person2 waits to get lock on sku-A since thread-x  has lock on it.

Note: OOTB we have releaseInventoryLocks which does nothing, it wont release locks. Locks are only released once the transaction is committed or roll backed.

Note: for some reason, because of out-of-memory or normal bounce if the instance is bought down, then the row-locks which are acquired will be still be locked forever.  If this updates are being done in a processor. Move this processor to last in the pipeline (and use sql JMS to update inventory status/level).