# 상품 배치 트랜잭션
## 문제
- 배치를 통해 상품을 5분에 한 번씩 전체 삭제하고 insert 하는데 배치가 돌 떄마다 메인 페이지에서 상품이 보이지 않는 현상이 발생함.
### 해결책
- 트랜잭션 격리수준 변경
- SERIALIZABLE로 변경? 어떤 사이드이펙트가 있을까...
```java
package fnf.wp.online_store_batch.sub.category.infrastructure.batch;
import fnf.wp.online_store_batch.sub.category.core.batch.GoodsImage;
import fnf.wp.online_store_batch.sub.category.core.batch.ToBeGoodsDsGateway;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import lombok.NonNull;
import lombok.val;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.ConcurrencyFailureException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.EnableRetry;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
@Service
@EnableRetry
@Retryable(
retryFor = ConcurrencyFailureException.class,
backoff = @Backoff(random = true, delay = 1000, maxDelay = 5000),
maxAttempts = 3
)
@Transactional(
transactionManager = "subTransactionManager",
isolation = Isolation.READ_COMMITTED,
timeout = 30
)
public class ToBeGoodsDataMapper implements ToBeGoodsDsGateway {
private final JdbcTemplate jdbcTemplate;
public ToBeGoodsDataMapper(@Qualifier("subDataSource") DataSource subDataSource) {
this.jdbcTemplate = new JdbcTemplate(subDataSource);
}
@Override
public void deleteImages(@NonNull List<String> erpGoodsNos) {
val placeholders = erpGoodsNos.stream().map(n -> "?").collect(Collectors.joining(","));
val sql = "delete from simple_goods_image where type != 'VIDEO' and erp_goods_no in ("
+ placeholders + ")";
jdbcTemplate.update(sql, erpGoodsNos.toArray());
}
@Override
public void deleteImages() {
jdbcTemplate.execute("delete from simple_goods_image");
}
@Override
public void insertImages(@NonNull List<GoodsImage> asIsImages) {
jdbcTemplate.batchUpdate(
"insert into simple_goods_image(erp_goods_no, url, sequence, cut, type, asis_composed_key)"
+ " values (?, ?, ?, ?, ?, ?)",
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, asIsImages.get(i).erpGoodsNo());
ps.setString(2, asIsImages.get(i).url());
ps.setLong(3, asIsImages.get(i).sequence());
ps.setString(4, asIsImages.get(i).cut().name());
ps.setString(5, asIsImages.get(i).type().name().replace("THNAIL", "THUMBNAIL"));
ps.setString(6, asIsImages.get(i).asIsComposedKey());
}
@Override
public int getBatchSize() {
return asIsImages.size();
}
});
}
@Override
public void deleteSizes(@NonNull List<String> erpGoodsNos) {
val placeholders = erpGoodsNos.stream().map(n -> "?").collect(Collectors.joining(","));
val sql = "delete from simple_goods_size where erp_goods_no in (" + placeholders + ")";
jdbcTemplate.update(sql, erpGoodsNos.toArray());
}
@Override
public void deleteSizes() {
jdbcTemplate.execute("delete from simple_goods_size");
}
@Override
public void insertSizes(@NonNull List<GoodsSize> asIsSizes) {
jdbcTemplate.batchUpdate(
"insert into simple_goods_size(erp_goods_no, name, status, sku_no)"
+ " values (?, ?, ?, ?)",
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, asIsSizes.get(i).erpGoodsNo());
ps.setString(2, asIsSizes.get(i).name());
ps.setString(3, asIsSizes.get(i).status().name());
ps.setString(4, asIsSizes.get(i).skuNo());
}
@Override
public int getBatchSize() {
return asIsSizes.size();
}
});
}
@Override
public void deleteGoods() {
jdbcTemplate.execute("delete from simple_goods");
}
@Override
public void insertGoods(@NonNull List<Goods> asIsGoods) {
jdbcTemplate.batchUpdate(
"insert into simple_goods("
+ "erp_goods_no, "
+ "design_group_no, "
+ "pc_name, "
+ "mobile_name, "
+ "sale_status, "
+ "retail_price, "
+ "customer_price, "
+ "color_code, "
+ "color_name, "
+ "registration_date, "
+ "is_display, "
+ "approval_code, "
+ "sale_code, "
+ "is_new, "
+ "new_start_date, "
+ "new_end_date, "
+ "color_group_code, "
+ "color_group_name, "
+ "color_group_hex, "
+ "tag_name, "
+ "tag_color, "
+ "mall_id, "
+ "is_reservation_sales_goods"
+ ") values ("
+ "?, ?, ?, ?, ?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
+ "?, ?, ?, ?)",
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, asIsGoods.get(i).erpGoodsNo());
ps.setString(2, asIsGoods.get(i).designGroupNo());
ps.setString(3, asIsGoods.get(i).pcName());
ps.setString(4, asIsGoods.get(i).mobileName());
ps.setString(5, asIsGoods.get(i).saleStatus().name());
ps.setInt(6, asIsGoods.get(i).retailPrice());
ps.setInt(7, asIsGoods.get(i).customerPrice());
ps.setString(8, asIsGoods.get(i).colorCode());
ps.setString(9, asIsGoods.get(i).colorName());
ps.setDate(10, Date.valueOf(asIsGoods.get(i).registrationDate().toLocalDate()));
ps.setBoolean(11, asIsGoods.get(i).isDisplay());
ps.setString(12, asIsGoods.get(i).approvalCode().name());
ps.setString(13, asIsGoods.get(i).saleCode().name());
ps.setBoolean(14, asIsGoods.get(i).isNew());
ps.setDate(15, Date.valueOf(asIsGoods.get(i).newStartDate()));
ps.setDate(16, Date.valueOf(asIsGoods.get(i).newEndDate()));
ps.setString(17, asIsGoods.get(i).colorGroupCode());
ps.setString(18, asIsGoods.get(i).colorGroupName());
ps.setString(19, asIsGoods.get(i).colorGroupHex());
ps.setString(20, asIsGoods.get(i).tagName());
ps.setString(21, asIsGoods.get(i).tagColor());
ps.setString(22, asIsGoods.get(i).mallId().name());
ps.setBoolean(23, asIsGoods.get(i).isReservationSalesGoods());
}
@Override
public int getBatchSize() {
return asIsGoods.size();
}
});
}
}
```
Sign in with Wallet
Connect another wallet