Prevent Concurrency Problems! A Practical Introduction to Transactions

PHP Conference Ehime 2026

Transcript

1. Prevent Concurrency Problems! A Practical Introduction to Transactions Shoichi Ochi (@occhi) PHP Conference Ehime 2026 2026-10-03
2. About Me Shoichi Ochi SmartBank, Inc. Software Engineer From: Saijo, Ehime Hobby: Weight training @ochi11181101 @sho-work 2
4. Transactions 4
5. Introduction "Transaction" A mechanism that treats multiple reads and writes as one logical unit of work. All reads and writes in a transaction are treated as a single unit, giving you an all-or-nothing guarantee. In other words, either everything succeeds (commit), or it is as if nothing happened (rollback). 5
6. Introduction "Transaction" A mechanism that treats multiple reads and writes as one logical unit of work. All reads and writes in a transaction are treated as a single unit, giving you an all-or-nothing guarantee. Wrap multiple queries in BEGIN ~ COMMIT In other words, either everything succeeds (commit), or it is as if nothing happened (rollback). 6
7. Today's talk is about the problems that happen when several of those transactions run at once 7
8. So, what problems actually happen? 🤔 8
9. Let's look at a simple example! 💪💪 9
10. Introduction A simple concurrency problem Consider two transactions that update the same counter row * https://www.oreilly.co.jp/books/9784873118703/ 10
11. Introduction A simple concurrency problem Consider two transactions that update the same counter row BEGIN * https://www.oreilly.co.jp/books/9784873118703/ COMMIT 11
12. Introduction A simple concurrency problem Consider two transactions that update the same counter row BEGIN * https://www.oreilly.co.jp/books/9784873118703/ COMMIT 12
13. Introduction A simple concurrency problem Consider two transactions that update the same counter row Tx A Tx B * https://www.oreilly.co.jp/books/9784873118703/ 13
14. Introduction A simple concurrency problem Both users want to add 1 to the current counter So we want this: • 43 right after User 1 commits • 44 right after User 2 commits * https://www.oreilly.co.jp/books/9784873118703/ 14
15. Introduction A simple concurrency problem 1. Tx A reads the counter (= 42) * https://www.oreilly.co.jp/books/9784873118703/ 15
16. Introduction A simple concurrency problem 1. Tx A reads the counter (= 42) 2. Tx B reads the counter (= 42) * https://www.oreilly.co.jp/books/9784873118703/ 16
17. Introduction A simple concurrency problem 1. Tx A reads the counter (= 42) 3. 42 + 1 → UPDATE to 43 2. Tx B reads the counter (= 42) * https://www.oreilly.co.jp/books/9784873118703/ 17
18. Introduction A simple concurrency problem 1. Tx A reads the counter (= 42) 3. 42 + 1 → UPDATE to 43 2. Tx B reads the counter (= 42) * https://www.oreilly.co.jp/books/9784873118703/ 4. 42 + 1 → UPDATE to 43 18
19. Introduction A simple concurrency problem The final result is 43, not 44! 😭 3. 42 + 1 → UPDATE to 43 4. 42 + 1 → UPDATE to 43 * https://www.oreilly.co.jp/books/9784873118703/ 19
20. Hmm... 🤔 2020
21. So how do we prevent this? 🤔 2121
22. Today's Talk 22
23. The isolation levels and race conditions covered today 23
24. The isolation levels and race conditions covered today 24
25. Today's Talk 25
26. 1. Transaction Isolation Levels 26
27. Transaction isolation levels
28. Transaction isolation levels 28
29. An isolation level sets "when other Txs' changes become visible" 29
30. When other Txs' changes start to be visible depends on the isolation level 30
31. When other Txs' changes start to be visible depends on the isolation level 31
32. When other Txs' changes start to be visible depends on the isolation level 32
33. Transaction isolation levels 33
34. There are 3 ways to implement Serializable 34
35. There are 3 ways to implement Serializable, and each behaves differently 35
36. Actual serial execution: never run them at the same time 36
37. Actual serial execution: never run them at the same time 37
38. There are 3 ways to implement Serializable, and each behaves differently 38
39. There are two kinds of locks: shared and exclusive 39
40. 2PL: take locks and make conflicting reads/writes wait 40
41. 2PL: take locks and make conflicting reads/writes wait 41
42. 2PL: take locks and make conflicting reads/writes wait 42
43. 2PL: take locks and make conflicting reads/writes wait 43
44. 2PL: take locks and make conflicting reads/writes wait 44
45. There are 3 ways to implement Serializable, and each behaves differently 45
46. SSI (serializable snapshot isolation): roll back if the snapshot has been changed 46
47. SSI (serializable snapshot isolation): roll back if the snapshot has been changed 47
48. SSI (serializable snapshot isolation): roll back if the snapshot has been changed 48
49. SSI (serializable snapshot isolation): roll back if the snapshot has been changed 49
50. SSI (serializable snapshot isolation): roll back if the snapshot has been changed 50
51. Today's Talk 51
52. 2. Concurrency Problems 52
53. The big picture of concurrency problems 53
54. For each problem, fill in the table: "from which level is it prevented?" 54
55. Problems when the same single row is read and written concurrently 55
56. For a while, we'll use the accounts table 56
57. Dirty Write: another Tx overwrites an uncommitted value 57
58. Dirty Write: another Tx overwrites an uncommitted value 58
59. Dirty Write: another Tx overwrites an uncommitted value 59
60. Dirty Write: another Tx overwrites an uncommitted value 60
61. Dirty Write: another Tx overwrites an uncommitted value 61
62. Dirty Write is prevented at every isolation level 62
63. How Dirty Write is prevented: hold the lock on written rows until COMMIT 63
64. Dirty Read: reading an uncommitted value 64
65. Dirty Read: reading an uncommitted value 65
66. Dirty Read: reading an uncommitted value 66
67. Dirty Read: reading an uncommitted value 67
68. Dirty Read: reading an uncommitted value 68
69. Dirty Read happens at Read Uncommitted and is prevented at Read Committed and above 69
70. Lost Update: overwriting based on a stale value, so an update is lost 70
71. Lost Update can happen at Snapshot Isolation and below (depends on the DB) https://www.postgresql.org/docs/current/transaction-iso.html 71
72. Problems that break consistency across multiple rows 72
73. Read Skew: one Tx reads committed data from different points in time 73
74. Read Skew: one Tx reads committed data from different points in time 74
75. Read Skew: one Tx reads committed data from different points in time 75
76. Read Skew: one Tx reads committed data from different points in time 76
77. Read Skew: one Tx reads committed data from different points in time 77
78. Read Skew: one Tx reads committed data from different points in time 78
79. Read Skew happens at Read Committed and below and is prevented at Snapshot Isolation and above 79 79
80. From here, we'll use the budgets table 80
81. Department budget: both decide "20 is still available," and the total exceeds the limit 81
82. Department budget: both decide "20 is still available," and the total exceeds the limit 82
83. Department budget: both decide "20 is still available," and the total exceeds the limit 83
84. Department budget: both decide "20 is still available," and the total exceeds the limit 84
85. Department budget: both decide "20 is still available," and the total exceeds the limit 85
86. Department budget: both decide "20 is still available," and the total exceeds the limit 86
87. No update was lost. What broke was the premise each Tx read 87
88. Write Skew happens in the flow "Observe → Decide → Mutate" 88
89. Write Skew happens in the flow "Observe → Decide → Mutate" 89
90. Write Skew happens in the flow "Observe → Decide → Mutate" 90
91. When two Txs observe the same stale premise, their combined result breaks the invariant 91
92. Problems of the same shape are all around us 92
93. For meeting room booking, we'll use the bookings table 93
94. Meeting room booking: both see "0 overlapping bookings" and book the same time 94
95. Meeting room booking: both see "0 overlapping bookings" and book the same time 95
96. Meeting room booking: both see "0 overlapping bookings" and book the same time 96
97. Meeting room booking: both see "0 overlapping bookings" and book the same time 97
98. Meeting room booking: both see "0 overlapping bookings" and book the same time 98
99. Write Skew where the row to lock doesn't exist yet 99
100. Only Serializable prevents Write Skew 100
101. What problems happen at the default isolation level? 101
102. Today's Talk 102
103. 3. Transaction Design Techniques to Prevent Concurrency Problems 103
104. Handle the problems left at the default isolation level yourself 104
105. Preventing Read Skew: read from a consistent snapshot, or keep the rows you read from changing 105
106. Preventing Lost Update: atomic updates, pessimistic locking, optimistic locking 106
107. Preventing Write Skew: does the row to lock exist yet, or not? 107
108. Preventing Write Skew: does the row to lock exist yet, or not? 108
109. Preventing Write Skew: does the row to lock exist yet, or not? 109
110. Preventing Write Skew: does the row to lock exist yet, or not? 110
111. Preventing Write Skew: does the row to lock exist yet, or not? 111
112. The department budget case is prevented by locking the premise rows with FOR UPDATE 112
113. The department budget case is prevented by locking the premise rows with FOR UPDATE 113
114. The department budget case is prevented by locking the premise rows with FOR UPDATE 114
115. The department budget case is prevented by locking the premise rows with FOR UPDATE 115
116. The department budget case is prevented by locking the premise rows with FOR UPDATE 116
117. Preventing Write Skew: does the row to lock exist yet, or not? 117
118. For username registration, we'll use the users table 118
119. Countermeasure 1: if a constraint can express it, leave it to the DB (UNIQUE username) 119
120. Countermeasure 1: if a constraint can express it, leave it to the DB (UNIQUE username) 120
121. Countermeasure 1: if a constraint can express it, leave it to the DB (UNIQUE username) 121
122. Countermeasure 1: if a constraint can express it, leave it to the DB (UNIQUE username) 122
123. An example with no row to lock 123
124. An example with no row to lock 124
125. An example with no row to lock 125
126. An example with no row to lock 126
127. An example with no row to lock 127
128. Countermeasure 4: turn "nothing there" into rows you can lock (materializing conflicts) 128
129. Countermeasure 4: turn "nothing there" into rows you can lock (materializing conflicts) 129
130. Countermeasure 4: turn "nothing there" into rows you can lock (materializing conflicts)
131. Countermeasure 4: turn "nothing there" into rows you can lock (materializing conflicts) 131
132. Countermeasure 4: turn "nothing there" into rows you can lock (materializing conflicts) 132
133. What to materialize: for each example, pick rows that always exist 133
134. For Write Skew, consider pessimistic locking → Serializable → materializing conflicts, in that order 134
135. Today's Talk 135
136. 4. Deadlocks and How to Handle Them 136
137. Deadlock: each waits for the other's lock, and neither can proceed 137
138. How deadlocks happen: two Txs lock rows in opposite order 138
139. How deadlocks happen: two Txs lock rows in opposite order 139
140. How deadlocks happen: two Txs lock rows in opposite order 140
141. How deadlocks happen: two Txs lock rows in opposite order 141
142. How deadlocks happen: two Txs lock rows in opposite order 142
143. How deadlocks happen: two Txs lock rows in opposite order 143
144. Design for both: make deadlocks less likely, and make them recoverable 144
145. Avoidance 1: lock in a consistent order 145
146. Avoidance 1: lock in a consistent order 146
147. Avoidance 1: lock in a consistent order 147
148. Avoidance 1: lock in a consistent order 148
149. Avoidance 1: lock in a consistent order 149
150. Avoidance 1: lock in a consistent order 150
151. Avoidance 2: keep Txs short to hold locks for less time 151
152. Avoidance 3: use indexes to narrow the rows you lock 152
153. Aside: other ways to avoid deadlocks Lowering the isolation level is also an option, but it's a last resort https://kaigionrails.org/2026/talks/sho-work/ 153
154. Aside: other ways to avoid deadlocks Coming up in a later talk! Lowering the isolation level is also an option, but it's a last resort https://kaigionrails.org/2026/talks/sho-work/ 154
155. Recovery: what happens when the DB detects a deadlock 155
156. Recovery: designing retries for deadlock failures 156
157. Recovery: a retry example in PHP (PDO) 157
158. Summary 158
159. The order of thinking when designing transactions that may hit concurrency problems 159
160. The order of thinking when designing transactions that may hit concurrency problems 160
161. The order of thinking when designing transactions that may hit concurrency problems 161
162. The order of thinking when designing transactions that may hit concurrency problems 162
163. Prevent Concurrency Problems! A Practical Introduction to Transactions Shoichi Ochi (@occhi) PHP Conference Ehime 2026 2026-10-03
164. Appendix - References • • • • • • • • • Designing Data-Intensive Applications (Martin Kleppmann, O'Reilly, 2017) ◦ https://www.oreilly.com/library/view/designing-data-intensive-applications/9781491903063/ Japanese edition: データ指向アプリケーションデザイン (O'Reilly Japan, 2019) ◦ https://www.oreilly.co.jp/books/9784873118703/ PostgreSQL: Transaction Isolation ◦ https://www.postgresql.org/docs/current/transaction-iso.html PostgreSQL: Explicit Locking ◦ https://www.postgresql.org/docs/current/explicit-locking.html PostgreSQL: Serialization Failure Handling ◦ https://www.postgresql.org/docs/current/mvcc-serialization-failure-handling.html MySQL 8.4: Transaction Isolation Levels ◦ https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html MySQL 8.4: InnoDB Locking ◦ https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html MySQL 8.4: Locking Reads ◦ https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-reads.html MySQL 8.4: Deadlocks in InnoDB ◦ https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html 164