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.