Browse Prior Art Database

System and method for allowing concurrent inventory updates

IP.com Disclosure Number: IPCOM000198575D
Publication Date: 2010-Aug-09
Document File: 7 page(s) / 99K

Publishing Venue

The IP.com Prior Art Database

Abstract

In some On-Line Transaction Processing (OLTP) relational databases, a single field in a record, is almost always updated in a single direction. This results in heavy contention for this single field, which can result in overall poor performance for any applications using this DB. This invention provides a solution to this problem.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 33% of the total text.

Page 1 of 7

System and method for allowing concurrent inventory updates

Disclosed is a system providing a capability to relieve the amount of locking required when a value must be continually decremented or incremented by tracking a number of transactions that are 'in-flight'. Using the disclosed system all updates in a single direction are performed through a concurrent write manager (CWM) tracking 'in-flight' updates enabling an update to a database in a single commit.

In a typical implementation of an on-line transaction processing (OLTP) relational database, a single field in a record, is usually updated in a single direction. The update results in heavy contention for the single field, which can result in overall poor performance for applications using the database.

For example, a Web commerce site that lists a sale for particular items a record + field 'INVENTORY.QUANTITY', might be continually decreased as the sale of the item continues. The quantity change frequently results in a great deal of locking for updates, because each update transaction must wait on all other previous update transactions (plus any other transaction against that resource) to complete before the update can acquire a lock.

In all relational database management systems (RDBMSs), write locks are incompatible with each other, causing connections to enter a lock-wait state until a lock is freed. Embodiments of the disclosed system relieve the amount of locking required when a value must be continually changed.

Embodiments of the disclosed system may be of particular importance to many large on-line retail applications that can experience potential loss of revenue due to basic locking problems. A typical example occurs during a sale of high-demand items; when many shoppers are competing to buy the same items, the system hangs waiting for inventory locks to be released. In severe cases, lock wait problem may significantly impact the ability to fulfil user purchase requests. Because of the business exposure of lock waits, a better lock handling solution is required.

By addressing a specific scenario, where a value is usually updated in a single direction, an embodiment of the disclosed system controls updates to allow for concurrent writes. A single direction is defined as movement along an ordered set, for example, decrementing a number, or incrementing the name of the month. An embodiment of the disclosed system is implemented at a database level by defining a new locking type and stored procedure.

An embodiment of the disclosed system tracks the number of 'in-flight' transactions. All updates in a single direction are then made through a concurrent write manager (CWM), which tracks the 'in-flight' updates, so the update to the database is made in a single commit.

In the example of Figure 1 a standard scenario in which each connection needs to wait for a write lock. In this example, 35 time units are required to complete all transactions.

1

Page 2 of 7

Figure 1

The e...