SQL Server BulkCopy할때 Hang이 걸리는 문제

💾 데이터베이스 Q&A Microsoft SQL Server Q&A
#1

OS는 Windows 2019 Server 에다가 SQL Server 2017 Express를 사용중입니다…

BulkCopy를 사용해서 데이터를 동기화하는데 함수를 아래와 같이 만들어 사용중인데 자꾸 오류가 발생하네요…


public bool BulkInsertDataTable(string tableName, DataTable dataTable)
{
    bool isSuccuss;
try
{
        using (SqlConnection SqlConnectionObj = new SqlConnection(global.LocalConnStr))
        {
            SqlConnectionObj.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction | SqlBulkCopyOptions.KeepIdentity, null))
            {
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.WriteToServer(dataTable);
                isSuccuss = true;
            }
        }
    }
    catch (Exception ex)
    {
        WriteLog(ex, visible: false);
        isSuccuss = false;
    }
    return isSuccuss;
}

문제가 되는 것은 bulkCopy.WriteToServer(dataTable); 이 부분입니다.

여기서 약 30초 간 Hang이 걸린 뒤에 오류를 뿜고 나가버리는데요…
(시작과 끝 부분에 시간을 변수에 담아놓고 비교해보니 33초가 걸렸다는 로그가 찍혀있습니다.)

오류메시지가 뭐였는지 잘 기억이 나질 않습니다ㅡ.ㅡ;;; 그냥 명령에 실패했다거나 시간을 초과했다는 메시지였던거 같아요…

(현장 가서 확인하느라 정신이 없어서 이 부분 메모를 깜빡해버렸어요…)

일단 구조상으로는 클라이언트로 명령을 내리면 클라이언트 8대가 동시에 각자 bulkCopy를 시도하며,

이상이 없는 상태에서는 0.5초 이내에 완료되는 작업입니다. 데이터 내용도 많지 않고요 (10row 미만)

Connection Leak가 발생하나 싶어서 테스트해봐도 재현은 안되니 참 미칠 노릇이네요…ㅎㅎ

Express의 한계로 인해 동시접속 문제이거나, 메모리의 한계일까요? (DB설정으로는 4기가에 맞춰놓았습니다.)

이런 문제를 본 적이 있거나 해결하신 경험이 있는 업계 선배님들의 조언 부탁드립니다…

#2

자세한 내용은 말씀하신대로 로그가 없어서 잘 모르겠지만

이 글이 혹시 도움이 되지 않을까요?

개발자 커뮤니티 SQLER.com - BULK INSERT 동시에 여러개 실행

SqlBulkCopy도 내부적으로는 SQL Server의 bcp 명령어를 사용하는 것일겁니다.

그런데 여담으로, BCP는 통상 1초에 10만 row 정도되는 양을 전송할 때 사용하는데 10 row 미만이면 그냥 반복문으로 insert 하는 게 추적도 용이하고 빠르지 않을까요?

좋아요 3
#3

Task로 주기적인 실행을 하는 패턴이라서 레이스가 발생할 가능성은 없지만…

주기적인 실행과 동시에 서버에서 명령이 날아오면 함수가 동시에 호출될 가능성이 있긴 합니다…

아무리 생각해도 이상한건 레이스가 발생하더라도 시간이 짧게 걸리는 작업인데 30초까지 걸릴 일인가… 싶고,

예상되는건 데드락이 생기는건데… 데드락이 생길만한 요소를 애초에 좀 차단해보고자

트랜잭션을 추가해서 작업해봤습니다. Truncate하기 전부터 락을 걸어놓고, Truncate 및 copy가 끝난 뒤에 풀리도록 해놨습니다.

다른 분들께도 꼭 도움 될거라 믿으며 소스 첨부합니다.


public bool BulkInsertDataTable(string tableName, DataTable dataTable)
{
    try
    {
        using (SqlConnection Conn = new SqlConnection(global.LocalConnStr))
        {
            Conn.Open();

            using (SqlTransaction transaction = Conn.BeginTransaction())
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Conn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.KeepIdentity, transaction))
                {
                    bulkCopy.BatchSize = 1000;
                    bulkCopy.BulkCopyTimeout = 5;
                    bulkCopy.DestinationTableName = tableName;

                    try
                    {
                        // 테이블을 먼저 비운다
                        using (SqlCommand cmd = new SqlCommand($"TRUNCATE TABLE {tableName}", Conn, transaction))
                        {
                            cmd.CommandTimeout = 3;
                            cmd.CommandType = CommandType.Text;
                            int affected = cmd.ExecuteNonQuery();
                        }
                        bulkCopy.WriteToServer(dataTable);
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        WriteLog(ex);
                        transaction.Rollback();
                        return false;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        WriteLog(ex);
        return false;
    }

    return true;
}

저도 경과는 지켜봐야 되어서… 일단 이렇게 수정해서 적용해보고, 추후 코멘트 다시 달겠습니다^^

좋아요 3
#4

10row인 경우도 있지만 10,000 Row가 넘는 경우도 있어서요…
링크 감사합니다.

좋아요 2
#5

이거도 참고하시면 좋을 것 같습니다.

좋아요 4
#6

혹시… BatchSize를 속성에 명시하지 않고, 수동으로 페이징 처리를 하신 이유가 있으신가요?

좋아요 1
#7

어떤 말씀이신지 잘 모르겠습니다.

보시는 대로 CommitBatchSize는 속성 처리되어있고 기본 값을 3000으로 준 것일 뿐, 사용자가 다시 설정해서 사용할 수 있습니다.

어느 부분 말씀하시는건가요?

좋아요 1
#8

아, 깃헙에 있는 소스를 보고 말씀드리는 겁니다^^;;
Commit 함수에

  for (int pageIndex = 0; pageIndex < numberOfPages; pageIndex++)
  {
      dt = InternalStore.Skip(pageIndex * CommitBatchSize).Take(CommitBatchSize).ToDataTable();
      BulkInsert(dt);
  }

이 부분요.

BCP BatchSize를 결정하는 속성이 있는데, 수동으로 DataTable의 Row범위를 나눠서 BulkInsert를 하신 이유가 있나 싶어서요

저는 아래와 같이 구현되어 있는데, 더 좋은 방법인가해서 여쭤봅니다.

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Conn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.KeepIdentity, transaction))
{
    bulkCopy.BatchSize = 1000;
좋아요 1
#9

저 소스의 의미는, 시스템에 따라 속도는 다를 수 있기 때문에 개발자가 디버깅을 하면서 배치 기본 사이즈를 알아서 맞추라는 의미입니다.

구현하신 대로 하셔도 되는데 그러면 배치 사이즈가 1000건으로 고정이 되죠.

제가 속성에 3000으로 기본값을 준 것은 수정하지 않고 사용하면 3000건으로 잡혀서 가지만 내가 전송할 DB 시스템이 속도 이슈가 있을 수 있다 싶을 경우 저 소스를 따로 수정하지 않고 BulkInsertHelper를 호출하는 부분에서 CommitBatchSize만 수정해서 배치 사이즈를 조절할 수 있는 것입니다.

또한 Skip Take를 사용한 부분은 DataTable에 한 번에 30000 건을 넣어서 메서드를 한 번만 호출하더라도 소스에서 지정한 CommitBatchSize 만큼 잘라서 페이징 형식으로 조절하여 전송하니까 DB입장에서도 부담이 덜할 것입니다.

결국 DB 입장을 고려해서 좀 더 유연하게 하기 위함입니다.

좋아요 2
#10

BatchSize를 지정할 수 있도록 해놓은 이유를 여쭤본 것이 아니라,
BatchSize를 지정해서 SqlBulkCopy클래스 + DB단에서 알아서 처리하도록 하는 방법이 있는데
수동으로 페이징 처리를 해서 작업을 나눠서 하는 특별한 이유가 있는지에 대한 질문이었어요.
DB 입장을 고려해서 처리하신거라고 하셨는데 실제 테스트를 해보면 수동으로 나눠서 처리하지 않는게 오히려 더 빠르고 DB에 부담을 덜 주는 것 같습니다…

좋아요 2
#11

아 이해했습니다.
SqlBulkCopy 클래스에 맡긴다는 생각은 못했네요.
그러면 지금 하신대로 하시는 게 더 빠를 수는 있을 것 같습니다.

그런데 그것에 대한 퍼포먼스 비교같은 게 있나요?

공유해주시면 좋은 자료가 될 것 같습니다.

좋아요 3
#12

예전에 테스트 겸 생성했던 프로젝트 파일이 있었는데, 지금은 다 날리고 없는거 같네요…
조만간 따로 글을 한번 올리도록 하겠습니다. 저도 검증을 다시 해보고 싶네요.
답변 주셔서 감사합니다!

좋아요 4