트랜잭션

트랜잭션을 사용하면 여러 SQL 문을 하나의 원자 단위로 데이터베이스에 커밋된 단일 작업 단위로 그룹화할 수 있습니다. 트랜잭션의 어떤 구문이라도 실패하면 이전 구문에서 이루어진 변경 사항이 롤백될 수 있습니다. 트랜잭션이 시작될 때 데이터베이스의 초기 상태가 유지됩니다. 트랜잭션을 사용하면 데이터베이스를 한 번에 여러 번 변경할 때 SQLite의 성능이 향상될 수도 있습니다.

동시성

SQLite에서는 한 번에 하나의 트랜잭션만 데이터베이스에 보류 중인 변경 내용을 가질 수 있습니다. 이 때문에 다른 트랜잭션이 완료되는 데 시간이 너무 오래 걸리면 BeginTransaction의 Execute 및 SqliteCommand 메서드 호출이 시간 초과될 수 있습니다.

잠금, 재시도 및 시간 제한에 대한 자세한 내용은 데이터베이스 오류를 참조하세요.

격리 수준

트랜잭션은 SQLite에서 기본적으로 직렬화할 수 있습니다 . 이 격리 수준은 트랜잭션 내에서 수행된 모든 변경 내용이 완전히 격리되도록 보장합니다. 트랜잭션 외부에서 실행되는 기타 명령문은 트랜잭션 변경 내용의 영향을 받지 않습니다.

또한 SQLite는 공유 캐시를 사용할 때 커밋되지 않은 읽기 를 지원합니다. 이 수준은 더러운 읽기, 비재현 읽기 및 유령 읽기를 허용합니다.

• 한 트랜잭션에서 보류 중인 변경 내용이 트랜잭션 외부의 쿼리에 의해 반환되지만, 해당 트랜잭션의 변경 내용이 롤백될 때 더티 읽기가 발생합니다. 결과에는 실제로 데이터베이스에 커밋되지 않은 데이터가 포함됩니다.

• 반복할 수 없는 읽기는 트랜잭션이 동일한 행을 두 번 쿼리할 때 발생하지만 다른 트랜잭션에 의해 두 쿼리 간에 변경되었기 때문에 결과가 다릅니다.

• 팬텀은 트랜잭션 중에 쿼리의 WHERE 절을 충족시키기 위해 변경되거나 추가되는 행입니다. 허용되는 경우 동일한 트랜잭션에서 두 번 실행될 때 동일한 쿼리가 다른 행을 반환할 수 있습니다.

Microsoft.Data.Sqlite는 전달된 IsolationLevel을 BeginTransaction 최소 수준으로 처리합니다. 실제 격리 수준은 커밋되지 않은 읽기 또는 직렬화 가능으로 승격됩니다.

다음 코드는 비정상적인 읽기를 모의합니다. 연결 문자열에는 다음이 포함되어 Cache=Shared야 합니다.

using (var firstTransaction = firstConnection.BeginTransaction())
{
    var updateCommand = firstConnection.CreateCommand();
    updateCommand.CommandText =
    @"
        UPDATE data
        SET value = 'dirty'
    ";
    updateCommand.ExecuteNonQuery();

    // Without ReadUncommitted, the command will time out since the table is locked
    // while the transaction on the first connection is active
    using (secondConnection.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        var queryCommand = secondConnection.CreateCommand();
        queryCommand.CommandText =
        @"
            SELECT *
            FROM data
        ";
        var value = (string)queryCommand.ExecuteScalar();
        Console.WriteLine($"Value: {value}");
    }

    firstTransaction.Rollback();
}

지연된 트랜잭션

Microsoft.Data.Sqlite 버전 5.0부터 트랜잭션을 지연할 수 있습니다. 그러면 첫 번째 명령이 실행될 때까지 데이터베이스의 실제 트랜잭션 생성이 지연됩니다. 또한 필요에 따라 트랜잭션이 읽기 트랜잭션에서 쓰기 트랜잭션으로 점진적으로 업그레이드됩니다. 트랜잭션 중에 데이터베이스에 대한 동시 액세스를 사용하도록 설정하는 데 유용할 수 있습니다.

using (var transaction = connection.BeginTransaction(deferred: true))
{
    // Before the first statement of the transaction is executed, both concurrent
    // reads and writes are allowed

    var readCommand = connection.CreateCommand();
    readCommand.CommandText =
    @"
        SELECT *
        FROM data
    ";
    var value = (long)readCommand.ExecuteScalar();

    // After a the first read statement, concurrent writes are blocked until the
    // transaction completes. Concurrent reads are still allowed

    var writeCommand = connection.CreateCommand();
    writeCommand.CommandText =
    @"
        UPDATE data
        SET value = $newValue
    ";
    writeCommand.Parameters.AddWithValue("$newValue", value + 1L);
    writeCommand.ExecuteNonQuery();

    // After the first write statement, both concurrent reads and writes are blocked
    // until the transaction completes

    transaction.Commit();
}

경고

데이터베이스가 잠겨 있는 동안 트랜잭션이 읽기 트랜잭션에서 쓰기 트랜잭션으로 업그레이드되는 경우 지연된 트랜잭션 내의 명령이 실패할 수 있습니다. 이 경우 애플리케이션은 전체 트랜잭션을 다시 시도해야 합니다.

세이브 포인트

Microsoft.Data.Sqlite 버전 6.0은 저장점을 지원합니다. 저장점을 사용하여 중첩된 트랜잭션을 만들 수 있습니다. 저장점은 트랜잭션의 다른 부분에 영향을 주지 않고 롤백할 수 있으며 저장점이 커밋(릴리스)될 수 있더라도 나중에 부모 트랜잭션의 일부로 변경 내용을 롤백할 수 있습니다.

다음 코드에서는 낙관적 오프라인 잠금 패턴을 사용하여 동시 업데이트를 검색하고 더 큰 트랜잭션의 일부로 저장점 내의 충돌을 해결하는 방법을 보여 줍니다.

using (var transaction = connection.BeginTransaction())
{
    // Transaction may include additional statements before the savepoint

    var updated = false;
    do
    {
        // Begin savepoint
        transaction.Save("optimistic-update");

        var insertCommand = connection.CreateCommand();
        insertCommand.CommandText =
        @"
            INSERT INTO audit
            VALUES (datetime('now'), 'User updates data with id 1')
        ";
        insertCommand.ExecuteScalar();

        var updateCommand = connection.CreateCommand();
        updateCommand.CommandText =
        @"
            UPDATE data
            SET value = 2,
                version = $expectedVersion + 1
            WHERE id = 1
                AND version = $expectedVersion
        ";
        updateCommand.Parameters.AddWithValue("$expectedVersion", expectedVersion);
        var recordsAffected = updateCommand.ExecuteNonQuery();
        if (recordsAffected == 0)
        {
            // Concurrent update detected! Rollback savepoint and retry
            transaction.Rollback("optimistic-update");

            // TODO: Resolve update conflicts
        }
        else
        {
            // Update succeeded. Commit savepoint and continue with the transaction
            transaction.Release("optimistic-update");

            updated = true;
        }
    }
    while (!updated);

    // Additional statements may be included after the savepoint

    transaction.Commit();
}