Блокировка данных в базах данных

Режимы блокировки

Режимы блокировки определяют разные типы блокировок. Выбор определенного режима блокировки зависит от типа ресурса, который требуется заблокировать. Для блокировок ресурсов уровня строки и страницы применяются следующие три типа блокировок:

Разделяемая блокировка (shared lock)

Резервирует ресурс (страницу или строку) только для чтения. Другие процессы не могут изменять заблокированный таким образом ресурс, но, с другой стороны, несколько процессов могут одновременно накладывать разделяемую блокировку на один и тот же ресурс. Иными словами, чтение ресурса с разделяемой блокировкой могут одновременно выполнять несколько процессов.

Монопольная блокировка (exclusive lock)

Резервирует страницу или строку для монопольного использования одной транзакции. Блокировка этого типа применяется инструкциями DML (INSERT, UPDATE и DELETE), которые модифицируют ресурс. Монопольную блокировку нельзя установить, если на ресурс уже установлена разделяемая или монопольная блокировка другим процессом, т.е. на ресурс может быть установлена только одна монопольная блокировка. На ресурс (страницу или строку) с установленной монопольной блокировкой нельзя установить никакую другую блокировку.

Блокировка обновления (update lock)

Может быть установлена на ресурс только при отсутствии на нем другой блокировки обновления или монопольной блокировки. С другой стороны, этот тип блокировки можно устанавливать на объекты с установленной разделяемой блокировкой. В таком случае блокировка обновления накладывает на объект другую разделяемую блокировку. Если транзакция, которая модифицирует объект, подтверждается, и у объекта нет никаких других блокировок, блокировка обновления преобразовывается в монопольную блокировку. У объекта может быть только одна блокировка обновления.

Система баз данных автоматически выбирает соответствующий режим блокировки, в зависимости от типа операции (чтение или запись). Блокировка обновления применяется для предотвращения определенных распространенных типов взаимоблокировок.

Возможность совмещения разных типов блокировок приводится в таблице ниже:

Матрица совместимости разных типов блокировок

	Разделяемая	Обновления	Монопольная
Разделяемая	Да	Да	Нет
Обновления	Да	Нет	Нет
Монопольная	Нет	Нет	Нет

Эта таблица интерпретируется следующим образом: предположим транзакция T1 имеет блокировку, указанную в заголовке соответствующей строки таблицы, а транзакция T2 запрашивает блокировку, указанную в соответствующем заголовке столбца таблицы. Значение «Да» в ячейке на пресечении строки и столбца означает, что транзакция T2 может иметь запрашиваемый тип блокировки, а значение «Нет», что не может.

Компонент Database Engine также поддерживает и другие типы блокировок, такие как кратковременные блокировки (latch lock) и взаимоблокировки (spin lock).

На уровне таблицы существует пять разных типов блокировок:

• разделяемая (shared, S);

• монопольная (exclusive, X);

• разделяемая с намерением (intent shared, IS);

• монопольная с намерением (intent exclusive, IX);

• разделяемая с монопольным намерением (shared with intent exclusive, SIX).

Разделяемые и монопольные типы блокировок для таблицы соответствуют одноименным блокировкам для строк и страниц. Обычно блокировка с намерением (intent lock) означает, что транзакция намеревается блокировать следующий нижележащий в иерархии объектов базы данных ресурс. Таким образом, блокировка с намерением помещается на уровне иерархии объектов, который выше того объекта, который этот процесс намеревается заблокировать. Это является действенным способом узнать, возможна ли подобная блокировка, а также устанавливается запрет другим процессам блокировать более высокий уровень, прежде чем процесс может установить требуемую ему блокировку.

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

Возможность совмещения разных типов блокировок

	S	X	IS	SIX	IX
S	Да	Нет	Да	Нет	Нет
X	Нет	Нет	Нет	Нет	Нет
IS	Да	Нет	Да	Да	Да
SIX	Нет	Нет	Да	Нет	Нет
IX	Нет	Нет	Да	Нет	Да

Создание таблиц базы данных с помощью SQL-запроса

Создание таблиц в графическом режиме, безусловно, удобно, однако не универсально. При использовании других средств разработки баз данных (например, IBM DB2) придется привыкать к новым приемам работы. Использование конструкций языка SQL позволяет работать с базами данных, исходя из единого подхода, в любой среде управления базами данных.

Выберите на панели инструментов «Создать запрос»:

Рисунок 11.

Создадим новую базу данных запросом. Напишем

CREATE DATABASE mbs21_query

и нажмем F5. В обозревателе объектов должна появиться новая база (если сразу не появилась, то надо выделить мышью раздел «Базы данных» и в контекстном меню выбрать «Обновить»).

Теперь создадим таблицу Speciality. Упрощенный синтаксис создания таблиц следующий:

CREATE TABLE <имя таблицы> (
    <имя столбца 1> <тип данных> NOT NULL DEFAULT <значение по умолчанию>,
    <имя столбца 2> <тип данных> NOT NULL DEFAULT <значение по умолчанию>,
    ...
)

Введем новый запрос:

/* создание таблицы Специальность*/
USE mbs21_query                                   -- определяем базу данных, в которую входит таблица
CREATE  TABLE  Speciality( 
    Num  INT IDENTITY(1,1) PRIMARY KEY NOT NULL, -- первичный ключ 
    NameSpec VARCHAR(60)                          -- название специальности
)

В обозревателе объектов видим, что таблица действительно создана. Файл с SQL-запросом сохраняем в своей папке (в конце работы необходимо показать запросы, которые были выполнены, преподавателю). Слово IDENTITY(1,1) добавлено, чтобы поле первичного ключа Num автоматически нумеровалось начиная с единицы (фактически, эта конструкция определяет спецификацию идентифицирующего столбца).

Таким же образом необходимо создать остальные таблицы. Рассмотрим таблицу Course.

Таблица Course (курс)

Имя поля (столбца)	Тип данных	Возможность содержать NULL
Num	Первичный ключ	int	нет
Name	Название специальности	varchar(60)	нет
YearEntry	Год поступления	int	нет
YearFinal	Год выпуска	int	да
Speciality	Специальность (внешний ключ ссылается на первичный ключ таблицы Speciality)	int	нет

Эта таблица содержит поле Speciality, которое ссылается на первичный ключ таблицы Speciality. Чтобы создать такую таблицу, необходимо выполнить запрос:

/* создание таблицы Курс */
USE mbs21_query                                  -- определяем базу данных, в которую входит таблица
CREATE  TABLE  Course( 
    Num  INT IDENTITY(1,1) PRIMARY KEY NOT NULL, -- первичный ключ
    YearEntry INT NOT NULL,                      -- год поступления
    YearFinal INT,                               -- год окончания
    Speciality INT FOREIGN KEY REFERENCES Speciality(Num) -- специальность,
    -- ссылка по внешнему ключу на поле Num таблицы Speciality
)

Примечание. Ссылку можно создать только на существующую таблицу. Задать ссылку по внешнему ключу можно и после создания таблицы (подробно будет рассмотрено в следующей лабораторной работе).

Задание. Создайте все остальные таблицы, указанные в Приложении, используя SQL – запросы.

Перенос базы данных доступа на SQL Server

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

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

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

На Выберите источник данных диалоговое окно, выберите Microsoft Access (ядро базы данных Microsoft Jet) из выпадающего списка.

Следующий на Имя файлае, нажмите на Просматривать и перейдите к базе данных Access, которую вы хотите импортировать, и нажмите открыто, Обратите внимание, что база данных не может быть в Access 2007 или более высоком формате (ACCDB) как SQL Server не распознает это! Поэтому, если у вас есть база данных Access с 2007 по 2016, сначала преобразуйте ее в База данных 2002-2003 формат (MDB) зайдя в Файл — Сохранить как,

Идите вперед и нажмите следующий выбрать пункт назначения. Поскольку вы щелкнули правой кнопкой мыши базу данных, в которую хотите импортировать данные, она уже должна быть выбрана в списке. Если нет, выберите Собственный клиент SQL от Пункт назначения падать. Вы должны увидеть экземпляр базы данных под Название сервера и затем сможете выбрать конкретную базу данных внизу, как только вы выберете метод аутентификации.

щелчок следующий а затем укажите способ передачи данных из Access в SQL, выбрав Скопируйте данные из одной или нескольких таблиц или Напишите запрос, чтобы указать данные для передачи,

Если вы хотите скопировать все таблицы или только некоторые таблицы из базы данных Access без каких-либо манипуляций с данными, выберите первый вариант. Если вам нужно скопировать только определенные строки и столбцы данных из таблицы, выберите второй вариант и напишите SQL-запрос.

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

Здесь у меня есть только одна таблица в моей базе данных Access. Нажмите Далее, и вы увидите Запустить пакет экран где Беги немедленно должны быть проверены.

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

щелчок близко и вы сделали. Теперь вы можете запустить SELECT для ваших таблиц, чтобы убедиться, что все данные были импортированы. Теперь вы можете использовать возможности SQL Server для управления вашей базой данных.

Есть проблемы с импортом данных из Access в SQL Server? Если да, оставьте комментарий, и я постараюсь помочь. Наслаждайтесь!

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Отключение эскалации блокировки

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

• Уровень таблицы: Можно отключить эскалацию блокировки на уровне таблицы. См. ALTER TABLE . SET (LOCK_ESCALATION = DISABLE) . Чтобы определить, какую таблицу нацелить, изучите запросы T-SQL. Если это невозможно, используйте расширенные события,в lock_escalation событие и изучите столбец object_id. Кроме того, используйте событие Lock:Escalation и изучите столбец с ObjectID2 помощью SQL Profiler.
• Уровень экземпляра: Вы можете отключить эскалацию блокировки, включив флаг трассировки 1211 для экземпляра. Однако этот флаг трассировки отключает всю эскалацию блокировки во всем мире в экземпляре SQL Server. Эскалация блокировки служит полезной цели в SQL Server за счет максимальной эффективности запросов, которые в противном случае замедляются из-за накладных расходов на приобретение и освобождение нескольких тысяч замков. Эскалация блокировки также помогает свести к минимуму требуемую память, чтобы отслеживать блокировки. Память, которую SQL Server динамически распределить для структур блокировки, является конечной. Поэтому, если отключить эскалацию блокировки и объем памяти блокировки будет достаточно большим, любая попытка выделить дополнительные блокировки для любого запроса может привести к сбойу и создать следующую запись ошибки:

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

Этот флаг трассировки (-T1211) можно добавить с помощью диспетчер конфигурации SQL Server. Необходимо перезапустить службу SQL Server, чтобы новый параметр запуска вступил в силу. При запуске DBCC TRACEON (1211, -1) запроса флаг трассировки вступает в силу немедленно. Однако если не добавить параметр запуска -T1211, при перезапуске службы SQL Server теряется DBCC TRACEON эффект команды. Включение флага трассировки предотвращает любые будущие эскалации блокировки, но не отменяет эскалации блокировки, которые уже произошли в активной транзакции.

Если используется подсказка блокировки, например ROWLOCK, это изменяет только первоначальный план блокировки. Подсказки блокировки не препятствуют эскалации блокировки.

Преобразование типов данных

Часто возникает необходимость конвертации значений одного типа в значение другого. Как же изменить тип данных в SQL?

Для выполнения конвертирования числа в символьные данные используют специализированную функцию STR. Чтобы выполнить другие преобразования, SQL Server содержит универсальные функции CAST и CONVERT, которые позволяют преобразовывать значения одного типа в значения другого типа. Эти функции взаимозаменяемы. Приведём пример:

CAST(выражение AS тип_данных) CONVERT(тип_данных, выражение )

Существует аргумент «стиль», который делает возможным управление стилями представления значений некоторых типов данных (нецелочисленные, дата и время, денежные).

DECLARE @d DATETIME DECLARE @s CHAR(8) SET @s=’29.10.01’ SET @d=CAST(@s AS DATETIME)2.3.

В данной статье мы постарались описать основные типы данных, которые традиционно используются в SQL

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

Обращайте внимание на ваши требования и подбирайте тип, позволяющий их реализовать

Часто встречающиеся ошибки 1С и общие способы их решения Промо

Статья рассчитана в первую очередь на тех, кто недостаточно много работал с 1С и не успел набить шишек при встрече с часто встречающимися ошибками. Обычно можно определить для себя несколько действий благодаря которым можно определить решится ли проблема за несколько минут или же потребует дополнительного анализа. В первое время сталкиваясь с простыми ошибками тратил уйму времени на то, чтобы с ними разобраться. Конечно, интернет сильно помогает в таких вопросах, но не всегда есть возможность им воспользоваться. Поэтому надеюсь, что эта статья поможет кому-нибудь сэкономить время.

254

Получение информации о текущих блокировках в СУБД MS SQL Server

Продолжая тему динамических представлений MS SQL Server, в данной статье будет описано как получить информацию о текущих блокировках СУБД.

Для большей информативности, текущая статья использует материал представленный в статье «Получение информации о текущих исполняемых запросах MS SQL Server», поэтому рекомендуется с ней ознакомиться.

Эксперимент

Произведем те же действия что и в приведенной выше статье, а именно:

1. Создадим тестовую базу и обработку
2. Выполним запись в регистр сведений через обработку и встанем на ожидании, не завершив транзакцию
3. В Management Studio выполним запрос выборки всех данных из таблицы регистра сведений
4. Получим текущие исполняемые запросы в СУБД

Получение информации о текущих блокировках СУБД

Текущее состояние нашей системы дает возможность выполнить запрос, который вернет информацию о текущих блокировках СУБД. В моем запросе будут использованы следующие представления:

Представление	Описание
dm_tran_locks	Возвращает сведения об активных в данный момент в SQL Server ресурсах диспетчера блокировок
partitions	Возвращает информацию о секциях
indexes	Возвращает информацию об индексах

Текст запроса приведен ниже, а также доступен во вложении к статье:

В условии отбора «MyBase» — имя базы данных в СУБД; «_InfoRg243» — условие по таблице.

Результат запроса к представлению dm_exec_requests (запрос из статьи, указанной в начале):

Текущие запросы СУБД

Результат запроса к представлению dm_tran_locks:

Запрошенные и установленные блокировки СУБД

Описания колонок результата запроса представлены ниже:

Имя колонки	Описание
DB_Name	Имя базы данных к которой выполняется запрос
TableName	Имя сущности в базе данных, с которой связан ресурс (в примере ожидается имя таблицы)
IndexID	Идентификатор индекса, связанного с таблицей
IndexName	Имя индекса, связанного с таблицей
ResourceType	Тип ресурса
RequestMode	Запрашиваемый/предоставленный режим запроса
RequestType	Тип запроса
RequestStatus	Текущее состояние запроса
RequestSessionID	Идентификатор сеанса, которому принадлежит этот запрос
ResourceDescription	Описание ресурса

Анализ полученного результата

Во-первых, интерпретируем результат запроса к представлению dm_tran_locks независимо от dm_exec_requests:

Как видно, сессия с идентификатором «65» установила (GRANT) на единственный индекс таблицы «_InfoRg243» следующие блокировки: IX на уровне страницы индекса и X на уровне ключа индекса. Как можно догадаться, это тот сеанс, который производит запись в регистр из 1С:Предприятие. Сессия с номером «55» установила (GRANT) блокировку IS на уровне страницы единственного индекса таблицы «_InfoRg243», а S блокировку на уровне ключа записи установить не удалось и поэтому она находится в ожидание (WAIT) до момента освобождения ресурса (или таймаута).

В результате данного запроса нет явного указания на связь заблокированного и блокирующего сеанса, хотя, при желании, сопоставив описания ресурсов, можно сделать вывод об этом. Поэтому, на мой взгляд, при разборе конфликтов блокировок логичнее сначала получить информацию по представлению dm_exec_requests, а затем уже получить информацию о том какие блокировки были установлены/запрошены источником и жертвой.

При таком подходе (сначала dm_exec_requests, затем dm_tran_locks) получим следующую интерпретацию:

Представление	Интерпретация результата
dm_exec_requests	Сеансом с идентификатором «55» (жертва) был отправлен запрос «Select * from dbo._InfoRg243», который встал на ожидании получения S блокировки по ресурсу «KEY: 9:72057594051559424 (227b7397de24)». Блокирующим является сеанс с идентификатором «65» (источник)
dm_tran_locks	Запрашиваемая блокировка сеанса «55» по ресурсу «227b7397de24»: S блокировка по ключу индекса «_InfoRg243_ByDims_N» таблицы «_InfoRg243». Как видно, причина по которой жертве не удается установить блокировку заключается в том что источник уже установил исключительную блокировку на данный ресурс. Установленная источником блокировка (X) несовместима с блокировкой жертвы (S)

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

Выводы

Механизм версионности при одинаковых нагрузках требует больше памяти. Если нет памяти, лучше решить вопрос установкой большего LOCK TIMEOUT (если приложение его выставляет).

Вопрос «что лучше: версионность или блокировки?» не решается, очевидно, в пользу версионности, вот почему:

Если памяти для СУБД мало — версионность работать будет, но пользоваться приложением будет нельзя (будет работать слишком медленно).Если памяти для СУБД много — время блокировок само по себе сократится (из-за ускорения записи и сокращения очередей на дисках) и преимущество неблокирующего чтения сойдет на нет.

В итоге, версионная у вас СУБД или блокирующая неважно, требуется адекватный объем памяти. Единственный плюс блокирующей СУБД, во всяком случае SQL Server перед Oracle в том, что при маленькой нагрузке можно работать на небольшом объеме памяти