Блокировка внешнего ключа SQL Server

Автор оригинала: Vlad Mihalcea.

Вступление

В этой статье мы рассмотрим, как ограничения внешнего ключа SQL Server блокируют родительскую запись при выполнении ОБНОВЛЕНИЯ дочерней записи.

Эта ситуация специфична для SQL Server и возникает даже при использовании уровня изоляции моментальных снимков, зафиксированных для чтения.

Таблицы базы данных

Давайте рассмотрим, что у нас есть следующие Сообщение и Комментарий к сообщению таблицы, которые образуют отношение “один ко многим” :

Столбец postID в таблице Комментарий к сообщению имеет отношение внешнего ключа к столбцу postID в таблице Сообщение .

Управление параллелизмом SQL Server

По умолчанию SQL Server использует 2PL (Двухфазную блокировку), что означает , что операция чтения получит Общую блокировку, в то время как операция записи получит Эксклюзивную блокировку.

Однако SQL Server также поддерживает MVCC (Управление параллелизмом нескольких версий) с помощью следующих двух уровней изоляции:

• Прочитайте зафиксированную изоляцию моментального снимка , которая позволяет оператору SQL находить базу данных на момент начала текущего выполняемого запроса
• Изоляция моментального снимка , которая позволяет оператору SQL находить базу данных по состоянию на начало текущей выполняемой транзакции

Чтобы переключиться с уровня изоляции по умолчанию на основе 2PL Read Committed на уровень изоляции моментальных снимков на основе MVCC Read Committed , вам необходимо включить свойство конфигурации READ_COMMITTED_SNAPSHOT , например:

ALTER DATABASE [high_performance_java_persistence] 
SET READ_COMMITTED_SNAPSHOT ON

Блокировка внешнего ключа SQL Server

Предполагая, что мы используем Уровень изоляции Зафиксированного моментального снимка//, давайте попробуем обновить запись в транзакции Алисы:

LOGGER.info(
    "Alice session id: {}",
    entityManager.createNativeQuery(
        "SELECT @@SPID"
    ).getSingleResult()
);
LOGGER.info("Alice updates the Post entity");

Post post = entityManager.find(Post.class, 1L);
post.setTitle("ACID");

entityManager.flush();

И Боб обновляет Комментарий к сообщению дочернюю строку, связанную с Сообщением записью, измененной Алисой:

LOGGER.info(
    "Bob session id: {}",
    _entityManager.createNativeQuery(
        "SELECT @@SPID"
    ).getSingleResult()
);
LOGGER.info("Bob updates the PostComment entity");

PostComment _comment = _entityManager.find(PostComment.class, 1L);
_comment.setReview("Great!");
    
_entityManager.flush();

Обычно вы ожидаете, что обе транзакции завершатся успешно, но на самом деле транзакция Боба блокируется транзакцией Алисы, как показано на следующей диаграмме:

При просмотре журнала мы видим, что ОБНОВЛЕНИЕ Боба действительно заблокировано и ждет, пока Алиса освободит Эксклюзивную блокировку, которую она приобрела для записи Post :

-- Alice session id: 58
-- Alice updates the Post entity

UPDATE Post 
SET Title = 'ACID'
WHERE PostID = 1

-- Bob session id: 60
-- Bob updates the PostComment entity

UPDATE PostComment 
SET PostID = 1, Review = 'Great!' 
WHERE PostCommentID = 1

| table_name | blocking_session_id | wait_type | resource_type | request_status | request_mode | request_session_id |
|------------|---------------------|-----------|---------------|----------------|--------------|--------------------|
|    dbo.Post|                   58|    LCK_M_S|            KEY|            WAIT|             S|                  60|

Причина, по которой ОБНОВЛЕНИЕ Боба запрашивает общую блокировку записи Post , заключается в том, что инструкция UPDATE включает столбец postID Внешний ключ.

В SQL Server при обновлении внешнего ключа, если связанный первичный ключ кластеризован, компонент database engine пытается получить общую блокировку записи кластеризованного индекса, чтобы гарантировать, что родительская строка не будет изменена до внесения изменений в дочернюю запись.

По умолчанию режим гибернации включает все столбцы сущностей при выполнении инструкции UPDATE, и это может увеличить вероятность блокировки.

Если вы используете Hibernate с SQL Server, вам следует использовать аннотацию @DynamicUpdate для сущностей, содержащих ассоциации @ManyToOne или @OneToOne.

Поэтому при добавлении @DynamicUpdate аннотации к Комментарию к сообщению сущности:

@Entity(name = "PostComment")
@Table(
    name = "PostComment",
    indexes = @Index(
        name = "FK_PostComment_PostID",
        columnList = "PostID"
    )
)
@DynamicUpdate
public class PostComment {

    @Id
    @Column(name = "PostCommentID")
    private Long id;

    @Column(name = "Review")
    private String review;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "PostID")
    private Post post;

    //Getters and setters omitted for brevity
}

И повторяя ваш тестовый случай, мы видим, что блокировки нет, и оба утверждения сразу завершаются:

-- Alice session id: 51
-- Alice updates the Post entity

UPDATE Post 
SET Title = 'ACID'
WHERE PostID = 1

-- Bob session id: 53
-- Bob updates the PostComment entity

UPDATE PostComment 
SET Review = 'Great!' 
WHERE PostCommentID = 1

Потрясающе, правда?

Вывод

Понимание того, как базовая система реляционных баз данных реализует свой механизм управления параллелизмом, очень важно, если вы хотите разработать высокопроизводительный уровень доступа к данным.

В SQL Server инструкция UPDATE, включающая столбец внешнего ключа, пытается получить Общую блокировку связанной родительской записи, и по этой причине ОБНОВЛЕНИЕ может быть заблокировано, если параллельная транзакция содержит Исключительную блокировку связанной родительской записи.

Для приложений гибернации рекомендуется использовать аннотации @DynamicUpdate для сущностей, содержащих ассоциации @ManyToOne или @OneToOne , чтобы уменьшить конкуренцию в родительских записях при обновлении дочерней сущности.