Double Eleven Technology Series: Flash Sales Optimization on PostgreSQL

11.11 The Biggest Deals of the Year. 40% OFF on selected cloud servers with a free 100 GB data transfer! Click here to learn more.

Flash sales has always been indispensable for e-commerce as well as for other online marketing efforts. For Alibaba, flash sales during the Double Eleven online shopping festival (Singles’ Day) on November 11 is a necessity. However, the scale of Alibaba’s Double Eleven poses significant challenges towards the servers and databases that support Alibaba’s e-commerce platform.

Due to the competitive nature of flash sales, it’s not unusual to see customers coming up with innovative but unfair ways to gain an upper hand over other customers. For example, on every November 11, Alibaba’s transaction platforms receive heavy stress from bots, scripts, and external plug-ins for automated rush purchasing systems.

To cope with these challenges, the team at Alibaba has come up with many ways to optimize flash sales. For example, databases may adopt the queuing mechanism, asynchronous messages, or transaction merging to optimize transactions. In this article, I will introduce a flash sales optimization measure called advisory locks (AD LOCK) for PostgreSQL.

Flash Sales Typical Scenario

Although you may already be very familiar with flash sales, I still want to introduce some relevant ideas to make this article complete.

For example, let’s imagine that there is one set of iPhone priced USD $1 for sale. This iPhone can be regarded as a record. When the flash sales starts, the customer who successfully buys this iPhone (updates the lock of this record) can win.

The challenge to the database is that the database must concurrently handle many update requests on only one record. Only one or a few requests can be successful, and the others fail or cannot update the record.

For example, if there are 100 iPhones for flash sales and 1 million customers participate in the flash sales, the minimum granularity for the database is a row lock. When one customer is updating this record, the other 999,999 customers are waiting. But ideally, the database should only process the 100 lucky winners in the flash sales. It is meaningless to process the requests from the customers who will not be receiving the iPhone as this will only waste resources.

Traditionally, a flag bit is used to indicate whether this record has been updated or how many times (how many iPhones) the record has been updated.

However, this method has the following defect:

The customer obtaining the lock may be successful or fail in handling this record, or may handle the record for a long time (because the database responds slowly). Before the transaction is finished, other sessions have to wait. Waiting is unnecessary and a waste of time for customers who did not obtain the lock.

Commonly Used Methods to Coping with Flash Sales

Generally, for update, nowait can be used to avoid waiting. That is, if a customer does not obtain the lock, the customer does not need to wait.

This method shortens the waiting time, because an error is returned when the lock is not obtained.

Another method is request merging. That is, multiple update requests are merged into one update request. This method requires a change to kernel and will damage ACID, because if the merged request fails, all the merged update requests fail. (This is different from group update. Group update will not damage the ACID.)

Next, let’s learn about ad lock.

What Is Ad Lock?

Ad lock is a user-oriented lightweight lock. Its object is an integer. The lock types include transaction, session, shared, and exclusive.

In a single database, a lock can be obtained with a unique integer value. If the integer value is repeated, a new lock can be added using TRY. FALSE is returned immediately when no lock is obtained.

NameReturn TypeDescriptionpg_advisory_lock(key bigint)voidObtain exclusive session level advisory lockpg_advisory_lock(key1 int, key2 int)voidObtain exclusive session level advisory lockpg_advisory_lock_shared(key bigint)voidObtain shared session level advisory lockpg_advisory_lock_shared(key1 int, key2 int)voidObtain shared session level advisory lockpg_advisory_unlock(key bigint)booleanRelease an exclusive session level advisory lockpg_advisory_unlock(key1 int, key2 int)booleanRelease an exclusive session level advisory lockpg_advisory_unlock_all()void Releaseall session level advisory locks held by the current sessionpg_advisory_unlock_shared(key bigint)booleanRelease a shared session level advisory lockpg_advisory_unlock_shared(key1 int, key2 int)booleanRelease a shared session level advisory lockpg_advisory_xact_lock(key bigint)voidObtain exclusive transaction level advisory lockpg_advisory_xact_lock(key1 int, key2 int)voidObtain exclusive transaction level advisory lockpg_advisory_xact_lock_shared(key bigint)voidObtain shared transaction level advisory lockpg_advisory_xact_lock_shared(key1 int, key2 int)voidObtain shared transaction level advisory lockpg_try_advisory_lock(key bigint)booleanObtain exclusive session level advisory lock if availablepg_try_advisory_lock(key1 int, key2 int)booleanObtain exclusive session level advisory lock if availablepg_try_advisory_lock_shared(key bigint)booleanObtain shared session level advisory lock if availablepg_try_advisory_lock_shared(key1 int, key2 int)booleanObtain shared session level advisory lock if availablepg_try_advisory_xact_lock(key bigint)booleanObtain exclusive transaction level advisory lock if availablepg_try_advisory_xact_lock(key1 int, key2 int)booleanObtain exclusive transaction level advisory lock if availablepg_try_advisory_xact_lock_shared(key bigint)booleanObtain shared transaction level advisory lock if availablepg_try_advisory_xact_lock_shared(key1 int, key2 int)booleanObtain shared transaction level advisory lock if available

Generally, the minimum lock (opened to customers) supported by database is row lock. Compared with LWLOCK and SPINLOCK, row lock is heavy. Therefore, row lock is often a bottleneck in flash sales, including the lock waiting time.

Purpose of Ad Lock

In addition to supporting flash sales, ad lock has the following functions:

  1. Concurrent security check
  2. UPSERT in recursive call
  3. Ensuring atomic operation in service logic design

Performance of Ad Lock

Ad lock is lightweight. It does not need to access data or execute much code, so it has a high efficiency.

A 32-core, 64-thread computer can handle 1.31 million requests per second.

Example of Using Ad Lock in Flash Sales

A product has a unique ID in the database. We can lock the ID (if the ID is repeated in different tables, add offset or use other measures to avoid conflict).

The row is locked and updated only when the ID is successfully locked. This avoids ineffective row lock waiting and redundant query code.

The QPS of ad lock in handling concurrent update requests for one record can reach 391,000/s. The state of product will be changed to sold out quickly, avoiding a waste of database resources.

Now, the database host has 66.2% available CPU resource.

Comparison with Traditional Method

Typically, select for update nowait is used to avoid waiting.

PG can use the do clauses to merge the preceding commands into one block.

The traditional method can handle 86,000 requests per second.

There is 54.5% available CPU resource.

Real Deduction Throughput of a Single Product

When testing the deduction throughput of a product, you can find that the QPS is high. However, the actual deduction throughput is lower because an error is returned if no lock is obtained.

The inventory of a single product is deducted 6112.49 times per second. Generally, the inventory of the products for flash sales is within 1000; otherwise, customers do not need to scramble for the products.

Therefore, this value can meet requirement.

Real Deduction Throughput of All Products on the Platform

Assume 10 million products are available on the platform. Test the total deduction throughput using this method.

Check the cnt for the real deduction statistics. On the entire platform, 272331.12 products are deducted per second. That is, 270,000 products are sold every second.

Advantages of Ad Lock Compared with Other Flash Sales Optimization Methods

Ad lock reduces CPU utilization and shortens waiting time to the minimum. According to the test case in this article, a record can be updated 391,000 times per second. However, the traditional method can reach only 86,000/s.

Furthermore, ad lock does not damage the ACID. A request affects a single transaction, and does not affect other transactions. In contrast, the merging method damages the ACID. If the merging fails, all merged request will fail.

To learn more about PostgreSQL on Alibaba Cloud, visit


Follow me to keep abreast with the latest technology news, industry insights, and developer trends.