Shopify mengganti sistem reservasi inventaris dari Redis ke MySQL

• Sistem reservasi inventaris adalah infrastruktur inti untuk mencegah oversell, yaitu penjualan ganda atas produk yang sama selama pemrosesan pembayaran, dan Shopify telah lama mengoperasikannya berbasis Redis
• Dengan memanfaatkan fitur SKIP LOCKED di MySQL 8, sistem didesain ulang dari model kolom jumlah per item menjadi struktur 1 baris per unit penjualan, sehingga performa tinggi dapat dicapai tanpa Redis
• Dengan menggabungkan teknik optimasi MySQL seperti primary key majemuk, tingkat isolasi READ COMMITTED, urutan lock yang konsisten, serta pemrosesan batch UNION ALL, mereka mengatasi kontensi lock dan deadlock
• Bottleneck yang sebenarnya bukan pada kueri reservasi, melainkan pada penguasaan koneksi, dan dengan menginstrumentasi seluruh jalur checkout mereka berhasil menurunkan pembacaan DB sebesar 50% dan transaksi sebesar 33%
• Pada puncak Black Friday 2025, sistem menangani penjualan sebesar $5,1 juta per menit sambil menjaga CPU writer di bawah 50% dan CPU reader di bawah 16%, melampaui target throughput

---

Latar belakang: persyaratan sistem pencegahan oversell

• Diperlukan sistem Oversell Protection yang menjamin stok benar-benar masih tersedia saat checkout selesai
  • Reserve: saat pembayaran dimulai, item tersebut dikunci sementara selama beberapa menit
  • Claim: saat pembayaran selesai, jumlah stok dikurangi secara permanen dari ledger inventaris
• Kesalahan tidak dapat ditoleransi di kedua arah
  • Jika salah, dua orang bisa membeli produk yang sama, atau barang dianggap habis meski stok masih ada sehingga menyebabkan kehilangan penjualan
• Persyaratan skala: Shopify menangani lebih dari 14% e-commerce di AS, dan pada Black Friday 2025 mencatat penjualan $5,1 juta per menit, naik 11% dibanding tahun sebelumnya
• Inventaris multi-lokasi, jaminan ACID, throughput tinggi, dan akurasi sebagai prioritas utama adalah persyaratan inti

Batasan model Redis sebelumnya

• Di Redis, tiap item memiliki key jumlah, dan reservasi diproses dengan DECR, sedangkan pelepasan diproses dengan INCR
• Masalah utamanya: data reservasi (Redis) dan ledger inventaris (MySQL) berada di sistem yang berbeda
  • Pada tahap claim, pembaruan MySQL dan pembersihan Redis tidak bisa dibungkus dalam satu transaksi atomik
  • Tergantung urutan eksekusi, bisa terjadi oversell (produk terjual tetapi ledger belum dikurangi) atau undersell (ledger sudah dikurangi tetapi masih berstatus reserved)
• Tidak memiliki dukungan kesadaran inventaris multi-lokasi, dan ada beban biaya operasional untuk klaster Redis terpisah

Solusi inti: redesain MySQL berbasis SKIP LOCKED

Struktur dasar: satu baris per unit (One Row Per Unit)

• Alih-alih kolom jumlah per item, dipilih struktur 1 baris untuk setiap unit yang bisa dijual
  • Misalnya item dengan stok 10 → 10 baris; jika 3 unit dipesan, maka 3 baris dipilih dan dipindahkan dalam satu transaksi
• Dengan menempatkan reservasi dan ledger inventaris di DB MySQL yang sama, reserve dan claim dapat diproses sebagai transaksi ACID, sehingga menghilangkan jenis bug yang sebelumnya muncul di Redis
• SKIP LOCKED: baris yang sedang dikunci oleh transaksi lain dilewati, dan baris yang tersedia langsung dikembalikan → mengurangi kontensi tanpa menunggu baris yang sama

Batas ukuran pool: maksimum 1.000 baris per lokasi

• Baris yang tersedia untuk setiap kombinasi item/lokasi dibatasi hingga maksimum 1.000 agar ukuran tabel dan performa pemindaian tetap terjaga
  • Contoh: mencegah situasi seperti stok 50.000 × 10 lokasi = 500.000 baris
• Saat pool habis, pemulihan inline replenishment dipicu; lock digunakan agar hanya satu transaksi yang melakukan replenishment sehingga mencegah thundering herd ketika banyak transaksi sekaligus memasukkan baris
• Jika pool benar-benar kosong, keterlambatan hanya terjadi pada reservasi tersebut, dan pembeli yang stoknya benar-benar masih ada tidak akan salah dianggap kehabisan stok

Empat keputusan teknis utama

1. Mengurangi jumlah lock dengan primary key majemuk

• Pada prototipe awal, saat ID auto-increment digunakan sebagai primary key, InnoDB mengunci baik secondary index maupun clustered index sehingga terjadi 2 row lock per reservasi
• Dengan menerapkan primary key majemuk yang terdiri dari shop_id, inventory_item_id, inventory_group_id, id, kolom filter ikut masuk ke primary key sehingga lock berkurang menjadi satu
• Di lingkungan reservasi ribuan kali per detik, desain indeks dan primary key secara langsung memengaruhi jumlah lock dan throughput

2. Menghapus gap lock dengan READ COMMITTED

• Saat SELECT ... FOR UPDATE SKIP LOCKED dijalankan pada tabel kosong, muncul gap lock (termasuk supremum), yang memblokir INSERT dari transaksi replenishment dan memicu deadlock
• Tingkat isolasi diubah dari default MySQL yaitu REPEATABLE READ menjadi READ COMMITTED → cara terjadinya gap lock berubah sehingga transaksi replenishment dapat berjalan normal
• Karena ini adalah penggunaan tingkat isolasi non-default pertama di codebase tersebut, dibutuhkan dukungan framework kecil untuk pengaturan tingkat isolasi per transaksi

3. Mencegah deadlock dengan urutan lock yang konsisten

• Reserve dan claim mengakses dua tabel dalam urutan yang berbeda, sehingga memicu deadlock
  • reserve: reserved_quantities INSERT → reservation_units DELETE
  • claim: reserved_quantities DELETE
• Solusinya: reserve selalu melakukan DELETE pada tabel units terlebih dahulu, lalu INSERT ke reserved_quantities setelahnya untuk menstandarkan urutan → menghilangkan circular wait

4. Mengurangi round trip dengan batch UNION ALL

• Saat ada beberapa line item dalam keranjang, kueri reservasi dibatch menjadi satu round trip menggunakan UNION ALL
• Pengurangan total round trip ini memperbaiki latensi saat sistem berada di bawah beban

Bottleneck yang sebenarnya: bukan kueri, melainkan penguasaan koneksi

Proses menemukan masalah

• Di lingkungan produksi, sistem mencapai batas sebelum target throughput tercapai, P90 latency tetap baik, CPU belum maksimal, dan kueri pun sudah dioptimalkan
• Gejala yang diamati dalam load test:
  • antrean thread di dalam MySQL
  • lonjakan CPU saat pekerjaan yang menumpuk di antrean mulai dieksekusi
  • koneksi backend MySQL habis di layer ProxySQL

Mendapatkan visibilitas koneksi

• Layer aplikasi: menambahkan komentar identifikasi proses bisnis ke semua pernyataan SQL dalam bentuk /* conn_tag:checkout_completion */
• Layer ProxySQL: menambahkan parsing tag serta agregasi waktu penguasaan koneksi per pemanggil
• Hasilnya: langsung terlihat proses mana yang menguasai koneksi dan berapa lama durasinya

Temuan dan penyelesaiannya

• Selain reservasi, kode lain di jalur checkout juga menguasai koneksi lebih lama daripada yang diperlukan
  • Kode-kode ini lolos dari target optimasi karena sebelumnya tidak lebih dulu mencapai batas
• Hasil penataan jalur checkout: pembacaan ke DB primer turun 50%, transaksi turun 33%
• Bottleneck tambahan dihapus dengan menyesuaikan pengaturan InnoDB thread concurrency, yang sudah lama dikonfigurasi secara konservatif dan tidak pernah ditinjau ulang
• Setelah perbaikan, pada flash sale bervolume tinggi CPU writer tetap di bawah 50% dan CPU reader di bawah 16%

Cara transisi: Shadow Mode

• Shopify tidak langsung beralih dari Redis ke MySQL, melainkan menjalankan kedua sistem secara paralel dengan Shadow Mode
  • Semua reservasi ditulis bersamaan ke Redis dan MySQL, sementara Redis tetap menjadi source of truth
  • Akurasi dan performa MySQL diverifikasi secara paralel di atas traffic produksi yang nyata
• Peralihan bisa dilakukan tanpa migrasi reservasi in-flight (karena kedua sistem hidup bersamaan)
• Bahkan setelah source of truth dipindahkan ke MySQL, kill switch tetap dipertahankan, dan lewat jalur dual write Redis selalu dijaga tetap mutakhir
• Rollout dilakukan bertahap per pod, mulai dari pod dengan trafik rendah hingga merchant dengan volume tertinggi

Pelajaran

1. Tinjau ulang keputusan lama

• Pemanfaatan MySQL yang mustahil 5 tahun lalu kini menjadi mungkin berkat fitur baru seperti SKIP LOCKED
• Pengaturan "aturan praktis" seperti batas thread perlu ditinjau ulang ketika workload dan hardware berubah
• Jika CPU rendah tetapi antrean tetap terjadi, penyebabnya harus benar-benar ditelusuri

2. Mulai dari kecil dan amati

• Mereka membangun prototipe minimum dengan skrip Ruby kecil dan MySQL tanpa full Rails framework
• Mengamati perilaku lock secara langsung dari terminal kedua memberi pelajaran yang lebih banyak daripada teori
• Pola instrumentasi penguasaan koneksi (tag di layer aplikasi + agregasi di proxy) mudah diimplementasikan dan bisa langsung diterapkan

[hso2341]

Sudah lama sekali akhirnya muncul tulisan yang benar-benar terasa seperti tulisan pengembangan.

[redmi]

Saya juga sudah lama tidak melihat tulisan seperti ini, jadi rasanya berbeda.
Sepertinya saya terlalu lelah dengan AI.

[malcomjj]

Saya setuju haha, ini benar-benar zaman ketika tulisan seperti ini terasa menyenangkan!

[goathead]

Ternyata bukan cuma saya yang lelah dengan tulisan promosi AI Slop wkwk

[kaydash]

Mantap!

[click]

Bukankah skip locked sudah ada sejak lama di PostgreSQL atau Oracle? Tapi di sisi lain, beban biaya lisensi Oracle jelas memberatkan, dan untuk PostgreSQL, karena implementasi MVCC, beban update tampaknya akan cukup besar, jadi saya kira itulah alasan mereka mengadopsinya setelah fitur tersebut hadir di MySQL 8.