๋ฌธ์ ์ํฉ
ํน์ ์ฌ์ฉ์์ ๋ฐ์ดํฐ๋ฅผ ์ผ๊ด ์ญ์ ํ๋ ๋ฐฐ์น์์ ๋ ์ข ๋ฅ์ ํ์์์์ด ๋ฐ์ํ๋ค.
Lock wait timeout exceeded; try restarting transaction ← ์๋ธ์ฟผ๋ฆฌ DELETE ๋จ๊ณ
Query execution was interrupted (max_statement_time exceeded) ← PK ๊ธฐ๋ฐ SELECT+DELETE ๋จ๊ณ
๋ ์๋ฌ๋ ๋์์ ๋ฐ์ํ ๊ฒ์ด ์๋๋ค. ์๋ธ์ฟผ๋ฆฌ DELETE๋ฅผ ์ฌ์ฉํ ๋ Lock wait timeout์ด ๋ฐ์ํ๊ณ , PK ๊ธฐ๋ฐ SELECT+DELETE๋ก ์ ํํ ๋ค max_statement_time exceeded๊ฐ ๋ฐ์ํ๋ค. ํ์์์ ์ข ๋ฅ๊ฐ ๋ฐ๋ ๊ฒ ์์ฒด๊ฐ ๋ถ์ ํฌ์ธํธ๋ค.
์ด๊ธฐ ์ฝ๋๋ ์๋ธ์ฟผ๋ฆฌ DELETE ๊ตฌ์กฐ์๋ค.
DELETE FROM products
WHERE id IN (
SELECT id FROM (
SELECT id FROM products
WHERE user_id = #{userId}
ORDER BY id
LIMIT 5000
) t
)
์ฒ์์๋ ์๋ธ์ฟผ๋ฆฌ๊ฐ ํ์์์์ ์์ธ์ด๋ผ๊ณ ์๊ฐํ๋ค. ํ์ง๋ง ์ฟผ๋ฆฌ ํํ๊ฐ ์๋๋ผ ์์คํ ์ ๊ตฌ์กฐ์ ๋ฌธ์ ์ ์ํด ๋ฐ์ํ ์ถฉ๋์ด์๋ค.
๊ฐ์ ํ ์ด๋ธ์ ์ค์๊ฐ ์๋น์ค๊ฐ ON DUPLICATE KEY UPDATE ๋ฒํฌ UPSERT๋ฅผ ์ง์์ ์ผ๋ก ์คํ ์ค์ด์๋ค.

์ค์๊ฐ ์๋น์ค์ ๋ฝ ํ๋
์ถฉ๋ ์์ธ์ ์ดํดํ๋ ค๋ฉด ์ค์๊ฐ ์๋น์ค์ ๋ฝ ํ๋ ๋ฐฉ์์ ๋จผ์ ํ์ ํด์ผ ํ๋ค.
@Transactional
public void saveProductData(List<Product> productList) {
sqlSession.insert("productMapper.saveProductData", productList);
}
sqlSession์ MyBatis SqlSessionTemplate์ผ๋ก Spring ํธ๋์ญ์ ์ ์๋ ์ฐธ์ฌํ๋ค. @Transactional ๋ฒ์ ์ ์ฒด ๋์ ์ปค๋ฅ์ ๊ณผ ๋ฝ์ด ์ ์ง๋๋ค.
์ค์ ์คํ ์ฟผ๋ฆฌ๋ <foreach> ๋ฒํฌ INSERT + ON DUPLICATE KEY UPDATE๋ค.
<insert id="saveProductData" parameterType="list">
INSERT INTO products (user_id, name, code, price, ...)
VALUES
<foreach item="p" collection="list" separator=",">
(#{p.userId}, #{p.name}, #{p.code}, #{p.price}, ...)
</foreach>
ON DUPLICATE KEY UPDATE
name = IF(status='N', name, VALUES(name))
, price = IF(status='N', price, VALUES(price))
, ...
</insert>
ON DUPLICATE KEY UPDATE๋ ๋ด๋ถ์ ์ผ๋ก ๋ ๊ฒฝ๋ก๋ก ๋ถ๊ธฐํ๋ค.

X lock์ INSERT/UPDATE ์๋ฃ ์์ ์ด ์๋๋ผ ํธ๋์ญ์ COMMIT ์์ ๊น์ง ์ ์ง๋๋ค. N๊ฑด ๋ฒํฌ๋ผ๋ฉด X lock์ด ์ต๋ N๊ฐ ๋์์ ์์ธ ์ฑ๋ก COMMIT๊น์ง ์ ์ง๋๋ค.
์ถฉ๋ ์์ธ ๋ถ์
1. Dirty Read - ๋ฏธ์ปค๋ฐ ์ ๊ท row
DB ๊ฒฉ๋ฆฌ ์์ค์ด READ_UNCOMMITTED์ด๋ฏ๋ก ๋ฐฐ์น SELECT๋ ์์ง ์ปค๋ฐ๋์ง ์์ row๋ ์ฝ๋๋ค.
์ค์๊ฐ ์๋น์ค๊ฐ ์ ๊ท row๋ฅผ INSERT ์ค์ธ ํธ๋์ญ์ (๋ฏธ์ปค๋ฐ)์ด X lock์ ๋ณด์ ํ ์ํ์ผ ๋, ๋ฐฐ์น SELECT๋ ๊ทธ row๋ฅผ Dirty Read๋ก ์ฝ๊ณ DELETE ๋์์ ํฌํจ์ํจ๋ค.

๊ฒฉ๋ฆฌ ์์ค์ด READ_COMMITTED์๋ค๋ฉด ์ปค๋ฐ๋ row๋ง ์ฝํ๋ฏ๋ก ๋ฏธ์ปค๋ฐ row๋ ์ ์ด์ SELECT์ ์กํ์ง ์์์ ๊ฒ์ด๋ค.
READ_UNCOMMITTED์ด๊ธฐ ๋๋ฌธ์ ์์ง ์กด์ฌํ์ง ์์์ผ ํ row๋ฅผ ์ฝ์ด์ DELETE๊น์ง ์๋ํ๊ฒ ๋ ๊ฒ์ด๋ค.
2. UPDATE ๊ฒฝ๋ก X lock - ๊ธฐ์กด row
์ค์๊ฐ ์๋น์ค์ UPSERT๋ ๊ธฐ์กด ๋ฐ์ดํฐ๊ฐ ์๋ ๊ฒฝ์ฐ UPDATE ๊ฒฝ๋ก๋ฅผ ํ๋ค. ์ด ๊ฒฝ์ฐ ์ถฉ๋ํ๋ row๋ ์ปค๋ฐ๋ ์ํ๋ค.

๊ธฐ์กด ๋ฐ์ดํฐ๊ฐ ๋ง์์๋ก UPDATE ๊ฒฝ๋ก ๋น์จ์ด ๋์์ง๊ณ , ๊ทธ๋งํผ X lock์ด ๋ง์ด ๊ฑธ๋ ค DELETE์์ ์ถฉ๋ ๊ฐ๋ฅ์ฑ์ด ์ฌ๋ผ๊ฐ๋ค.
3. ๊ฐญ ๋ฝ(Gap Lock) — ๋ฒ์ ์ค์บ
๊ธฐ์กด ์๋ธ์ฟผ๋ฆฌ DELETE๋ ์ฟผ๋ฆฌ ํ๋๋๊ฐ user_id ์ธ๋ฑ์ค ๋ฒ์ ์ค์บ์ ์ ํํ ๊ฐ๋ฅ์ฑ์ด ๋๋ค. ์ด ๊ณผ์ ์์ InnoDB๊ฐ Next-Key Lock(record + gap)์ ์ค์ ํ๋ค.
| ๋ฝ ์ข ๋ฅ | ๋์ | ์ค๋ช |
| Record Lock | ๋ ์ฝ๋ ์์ฒด | ํน์ row์ ๋ํ X/S ๋ฝ |
| Gap Lock | ๋ ์ฝ๋ ์ฌ์ด ๊ฐ๊ฒฉ | ํด๋น ๊ฐ๊ฒฉ์ INSERT ์ฐจ๋จ |
| Next-Key Lock | ๋ ์ฝ๋ + ์ ๊ฐ๊ฒฉ | Record Lock + Gap Lock ์กฐํฉ (InnoDB ๊ธฐ๋ณธ) |
INSERT ์คํ ์ InnoDB๋ ์ค์ ๋ ์ฝ๋ ๋ฝ ์ ์ INSERT INTENTION LOCK์ ํด๋น ๊ฐ๊ฒฉ์ ๊ฑด๋ค. INSERT INTENTION LOCK๋ผ๋ฆฌ๋ ํธํ๋์ง๋ง, ํด๋น ๊ฐ๊ฒฉ์ GAP LOCK์ด ๊ฑธ๋ ค ์์ผ๋ฉด ๋ธ๋กํน๋๋ค.

ํด๊ฒฐ ๊ณผ์
1. SELECT+DELETE ๋ถ๋ฆฌ + id(PK) ๊ธฐ๋ฐ ์ญ์
์๋ธ์ฟผ๋ฆฌ DELETE๋ฅผ SELECT์ DELETE๋ก ๋ถ๋ฆฌํ๊ณ , id(PK)๋ก ์ญ์ ๋์์ ์ง์ ํ๋ค.
<select id="selectProductIds" parameterType="map" resultType="long">
SELECT id FROM products
WHERE user_id = #{userId}
LIMIT 5000
</select>
<delete id="deleteProductsBatch" parameterType="list">
DELETE FROM products
WHERE id IN
<foreach collection="list" item="id" open="(" separator="," close=")">#{id}</foreach>
</delete>
์ด ๋ฐฉ์์์ ๊ฐญ ๋ฝ์ ๋ฐ์ํ์ง ์๋๋ค. InnoDB๋ ์ ๋ํฌ ์ธ๋ฑ์ค(PK ํฌํจ) ๋จ๊ฑด ์กฐํ์์ ๋ ์ฝ๋ ๋ฝ๋ง ๊ฑธ๊ณ ๊ฐญ ๋ฝ์ ์ค์ ํ์ง ์๋๋ค. ๊ฐญ ๋ฝ์ ๋ฒ์ ์ค์บ์ด๋ ๋น๊ณ ์ ์ธ๋ฑ์ค ์ค์บ์์ ์ฌ์ด์ ์ row๊ฐ ๋ผ์ด๋ค ์ ์๋๋ก ๋ง๊ธฐ ์ํด ์ค์ ํ๋ ๊ฒ์ธ๋ฐ, PK ํฌ์ธํธ ๋ฃฉ์ ์ ๊ตฌ๊ฐ์ ์ค์บํ์ง ์๊ธฐ ๋๋ฌธ์ด๋ค.

์๋ฌ ์ข ๋ฅ๊ฐ ๋ฐ๋์๋ค. ๊ฐญ ๋ฝ์ ์ ๊ฑฐ๋์ง๋ง, Dirty Read์ UPDATE ๊ฒฝ๋ก X lock ์ถฉ๋์ ์ฌ์ ํ ๋จ๋๋ค. ๊ทธ๋ฐ๋ฐ ์ด ๋จ๊ณ์์ ์๋ฌ ์ข ๋ฅ๊ฐ Lock wait timeout์์ max_statement_time exceeded๋ก ๋ฐ๋๋ค.
์ ํ์์์ ์ข ๋ฅ๊ฐ ๋ฐ๋์๋
์๋ธ์ฟผ๋ฆฌ DELETE๋ user_id ๋ฒ์ ์ค์บ ์ค GAP LOCK์ด ์ฒซ ๋ฒ์งธ ์ถฉ๋ ์ง์ ์์ ์ฆ์ ๋ธ๋กํน์ ๊ฑธ์๋ค. ๋จ ํ ๋ฒ์ ๋ฝ ๋๊ธฐ๊ฐ innodb_lock_wait_timeout์ ๋์ผ๋ฉด์ Lock wait timeout์ด ๋ฐ์ํ๋ค.
์๋ธ์ฟผ๋ฆฌ DELETE:
๋ฒ์ ์ค์บ → GAP LOCK → ์ค์๊ฐ INSERT์ ์ถฉ๋
→ ๋จ์ผ ๋๊ธฐ → 50์ด ์ด๊ณผ → Lock wait timeout exceeded
PK ๊ธฐ๋ฐ DELETE WHERE id IN (id_1, ..., id_5000)๋ก ๋ฐ๊พธ๋ฉด ๊ฐญ ๋ฝ์ด ์ฌ๋ผ์ง๋ค. ํ์ง๋ง Dirty Read๋ก ์ฝํ ๋ฏธ์ปค๋ฐ row๋ค์ด id ๋ฆฌ์คํธ์ ํฌํจ๋ ์ํ์ด๋ฏ๋ก, DELETE๋ 5000๊ฐ row์ X lock์ ํ๋์ฉ ํ๋ ์๋ํ๋ค.
์ค์๊ฐ ์๋น์ค๊ฐ ๊ฐ row๋ฅผ ์งง๊ฒ ์ก๊ณ ๋นจ๋ฆฌ ์ปค๋ฐํ๋ค๋ฉด ๊ฐ๋ณ ๋ฝ ๋๊ธฐ๋ 40์ด์ ๋ชป ๋ฏธ์น๋ค. ๊ทธ๋ฌ๋ 5000๊ฐ row๋ฅผ ์ฒ๋ฆฌํ๋ ๋์ ์งง์ ๋๊ธฐ๊ฐ ์์ญ~์๋ฐฑ ๋ฒ ๋์ ๋๋ฉด statement ์ ์ฒด ์คํ ์๊ฐ์ด max_statement_time์ ์ด๊ณผํ๋ค.
PK ๊ธฐ๋ฐ DELETE:
row 1: ๋ฝ ํ๋ (์ฆ์)
row 2: 2์ด ๋๊ธฐ → ํ๋
row 3: ๋ฝ ํ๋ (์ฆ์)
...
row 847: 5์ด ๋๊ธฐ → ํ๋
...
→ ๊ฐ๋ณ ๋๊ธฐ๋ 40์ด ๋ฏธ๋ง
→ ๋์ ์คํ ์๊ฐ์ด max_statement_time ์ด๊ณผ
→ Query execution was interrupted (max_statement_time exceeded)
๊ฐญ ๋ฝ ์ ๊ฑฐ๋ก ๋น ๋ฅธ ๋จ์ผ ๋ธ๋กํน์ ์ฌ๋ผ์ก์ง๋ง, Dirty Read row๋ค์ ๋ํ ๋ฐ๋ณต ๋ฝ ์๋๊ฐ statement ์คํ ์๊ฐ ์์ฒด๋ฅผ ๊ธธ๊ฒ ๋๋ฆฐ ๊ฒ์ด๋ค.
2. updated_at < baseTime ์กฐ๊ฑด ์ถ๊ฐ
๋ฐฐ์น ์์ ์์ ์ baseTime์ผ๋ก ๊ณ ์ ํ๊ณ , SELECT ์ ํด๋น ์๊ฐ ์ด์ ๋ฐ์ดํฐ๋ง ์กฐํํ๋ค.
<select id="selectProductIds" parameterType="map" resultType="long">
SELECT id FROM products
WHERE user_id = #{userId}
AND updated_at < #{baseTime}
LIMIT 5000
</select>
String baseTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
Map<String, Object> param = new HashMap<>();
param.put("userId", userId);
param.put("baseTime", baseTime);
List<Long> ids;
do {
ids = sqlSession.selectList("productMapper.selectProductIds", param);
if (!ids.isEmpty()) {
sqlSession.delete("productMapper.deleteProductsBatch", ids);
}
} while (!ids.isEmpty());
Dirty Read (๋ฏธ์ปค๋ฐ ์ ๊ท row) → ํด์
์ ๊ท INSERT row์ updated_at์ baseTime ์ดํ๋ค. Dirty Read๋ก ์ฝํ๋ updated_at < baseTime ์กฐ๊ฑด์์ ๊ฑธ๋ฌ์ง๋ฏ๋ก DELETE ๋์์ ํฌํจ๋์ง ์๋๋ค.

UPDATE ๊ฒฝ๋ก X lock (๊ธฐ์กด row) → ์์ ํด์ ๋ถ๊ฐ
๊ธฐ์กด row๋ updated_at < baseTime์ด๋ฏ๋ก SELECT์ ํฌํจ๋๋ค. SELECT์ DELETE ์ฌ์ด ํ์ด๋ฐ์ ์ค์๊ฐ ์๋น์ค๊ฐ ๊ฐ์ row์ X lock์ ํ๋ํ๋ฉด ์ถฉ๋์ด ๋ฐ์ํ๋ค.

baseTime ํํฐ๋ ์ด๋ฌํ ํ์ด๋ฐ ๊ฒฝํฉ์ ์ฐจ๋จํ์ง ๋ชปํ๋ค. Dirty Read ์ผ์ด์ค๋ '์ฝํ๋ฉด ์ ๋ row๋ฅผ ์ฝ๋' ๋ฌธ์ ์์ง๋ง, ์ด ์ผ์ด์ค๋ '์ฝํ์ผ ํ row์ธ๋ฐ DELETE ์ง์ ์ ๋ฝ์ด ๊ฑธ๋ฆฐ' ํ์ด๋ฐ ๋ฌธ์ ์ด๊ธฐ ๋๋ฌธ์ด๋ค.
์์ง ์ด ์ผ์ด์ค์์ ๋ฌธ์ ๊ฐ ๋ฐ์ํ์ง ์์ ๊ฒ์ SELECT → DELETE ์ฌ์ด ํ์ด ์ ๋ฐ๋ฆฌ์ด๋ก ๋งค์ฐ ์งง๊ณ , INSERT๋ณด๋ค UPDATE ๊ฒฝ๋ก๊ฐ ์ ๊ฒ ๋ฐ์ํ๊ธฐ ๋๋ฌธ์ผ๋ก ๋ณด์ธ๋ค. ํ์ง๋ง ํธ๋ํฝ์ด ๋๊ฑฐ๋ ๋ฐฐ์น ๋์๊ณผ ์ค์๊ฐ ์๋น์ค๊ฐ ๊ฒน์น๋ user_id๊ฐ ๋ง์์ง๋ฉด ์ธ์ ๋ ๋ฌธ์ ๊ฐ ์๊ธธ ์ ์๋ค.
๋ง์น๋ฉฐ
์ด๋ฒ ํธ๋ฌ๋ธ์ํ ์ ํต์ฌ์ ํ๋์ ํ์์์์ด ์๋๋ผ ์ธ ๊ฐ์ง ๋ฝ ์ถฉ๋ ๊ฒฝ๋ก๊ฐ ๋์์ ์๋ํ๋ค๋ ์ ์ด๋ค.
๊ฐญ ๋ฝ์ PK ๊ธฐ๋ฐ WHERE id IN (...) ์ผ๋ก ์ ํํด ์์ ํ ์ ๊ฑฐํ๊ณ , Dirty Read๋ updated_at < baseTime ํํฐ๋ก DELETE ์๋ ์์ฒด๋ฅผ ๋ง์๋ค. UPDATE ๊ฒฝ๋ก X lock ์ถฉ๋๋ง์ด ๋จ๋๋ค. ๊ฒฉ๋ฆฌ ์์ค ๋ณ๊ฒฝ, ์คํ ์๊ฐ ๋ถ๋ฆฌ ๋ฑ ์ฌ๋ฌ ๋ฐฉ๋ฒ์ด ์์ง๋ง ํ์ฌ ์์คํ ํ๊ฒฝ์์๋ ์ ์ฉํ ์ ์์ด ์ด ๊ธฐ๋ฅ์ ์ฌ๊ธฐ์ ๋ง๋ฌด๋ฆฌํ๋ค.