Блокировки в MS SQL Server 2000
Алексей Ширшов
Введение
Обычно блокировки рассматривают совместно с транзакциями. В данной статье упор делается в основном на механизм блокировок, его внутреннее устройство и использование в СУБД MS SQL Server 2000. Предполагается, что читатель хорошо знаком с транзакциями и их свойствами. Давайте вспомним вкратце, какими свойствами должны обладать транзакции в современных СУБД (эти требования носят название ACID – Atomicity, Consistency, Isolation и Durability):
Atomicity (атомарность). Это требование заключается в том, что все данные, с которыми работает транзакция, должны быть либо подтверждены (commit), либо отменены (rollback). Не должно быть ситуации, когда часть изменений подтверждается, а часть – отменяется. Это правило автоматически выполняется для простых данных.
Consistency (согласованность). После выполнения транзакции все данные должны остаться в согласованном состоянии. Другими словами, транзакция либо не изменит данных, и они останутся в прежнем состоянии, либо измененные данные будут удовлетворять ограничениям целостности, правилам (rules) и другим критериям согласованности данных.
Isolation (изолированность). Транзакции должны выполнятся автономно и независимо от других транзакций. При одновременном выполнении множества конкурирующих друг с другом транзакций, любое обновление определенной транзакции будет скрыто от остальных до тех пор, пока эта транзакция не будет зафиксирована. Существуют несколько уровней изолированности (изоляции) транзакций, которые позволяют выбрать наиболее оптимальное решение с точки зрения производительности и целостности данных. Основным методом реализации этих уровней и являются блокировки, о которых пойдет речь в этой статье.
Durability (долговечность или устойчивость). Это требование заключается в том, что после подтверждения изменения данных (фиксации транзакции) система переходит в новое состояние и возврат к старому состоянию не возможен, т.е. не возможен откат в предыдущее состояние. Для вложенных транзакций это требование нарушается.
В данной статье рассматриваются механизмы реализации уровней изолированности транзакции. Стандартом ANSI были определены четыре уровня изоляции транзакций. Первый – это нулевой уровень изоляции, второй – первый уровень и так далее. Эти уровни помогают решать различные проблемы, которые будут рассматриваться подробно далее в процессе написания демонстрационной программы на С++. Определения уровней будут даны в конце раздела.
Итак, чтобы лучше понять проблемы изоляции транзакций, рассмотрим их сначала с точки зрения программирования на С++. Так как наша программа будет оперировать простыми данными (значение типа int), будем считать, что требования атомарности выполняются автоматически. Кроме того, мы не будем налагать каких-либо логических ограничений на значение переменной и не будем использовать вложенных транзакций, так что требования согласованности и устойчивости также будут опущены.
Наша программа содержит всего два класса: CObject и CProxy. Класс CObject - это объект-одиночка (singleton), который содержит переменную value (доступ к этой переменной мы и будем защищать), и некоторый набор служебных функций. Класс CProxy представляет собой посредника для объекта CObject; именно с ним будет работать клиент. Вот первоначальный набросок (в классе CProxy используется нестандартная конструкция __declspec(property), поддерживаемая только компиляторами от Microsoft):
class CObject;
class CProxy
{
friend class CObject;
public:
__declspec(property(get=get_Value,put=put_Value)) int value;
int get_Value(int level = -1) const;
void put_Value(int i);
void Commit();
void Rollback();
private:
int _level;
int _value;
bool fUpd;
CProxy(CObject* par,int level)
{
fUpd = false;
parent = par;
_level = level;
}
CObject* parent;
};
class CObject
{
friend class CProxy;
public:
static CProxy& GetObject(int level = -1);
~CObject()
{
if (hMutex) CloseHandle(hMutex);
}
protected:
CProxy& BeginTran(int level)
{
return *(new CProxy(this,level));
}
void RequestExclusive(int level)
{
}
void RequestShared(int level)
{
}
void RemoveShared(int level)
{
}
void RemoveLocks()
{
}
private:
CObject()
{
value = 0;
}
int value;
static HANDLE hMutex;
};
__declspec(selectany) HANDLE CObject::hMutex = NULL;
CProxy& CObject::GetObject(int level)
{
HANDLE hLocMutex = CreateMutex(NULL,TRUE,_T("Guard-Lock-Mutex"));
bool flg = GetLastError() == ERROR_ALREADY_EXISTS;
if (flg) WaitForSingleObject(hLocMutex,INFINITE);
else CObject::hMutex = hLocMutex;
static CObject obj;
ReleaseMutex(hLocMutex);
if (flg) CloseHandle(hLocMutex);
return obj.BeginTran(level);
}
void CProxy::Commit()
{
parent->RemoveLocks();
delete this;
}
void CProxy::Rollback()
{
if (fUpd)
parent->value = _value;
parent->RemoveLocks();
delete this;
}
void CProxy::put_Value(int i)
{
parent->RequestExclusive(_level);
if (!fUpd)
_value = parent->value;
parent->value = i;
fUpd = true;
}
int CProxy::get_Value(int level) const
{
if (level == -1)
level = _level;
parent->RequestShared(level);
int v = parent->value;
parent->RemoveShared(level);
return v;
}
Клиент никогда не имеет дела непосредственно с экземпляром класса CObject. Экземпляры класса CProxy – представляют копию данных объекта CObject и делегируют запросы на чтение и запись переменной value. Код получился несколько громоздким: к чему такие сложности? Я заранее определил довольно широкий интерфейс, чтобы потом меньше исправлять. :)
Прошу обратить внимание на довольно сложный механизм создания экземпляра CObject в функции GetObject. Обычно в программах используется более простой код, типа:
CProxy& CObject::GetObject(int level)
{
static CObject obj;
return obj.BeginTran(level);
}
Чем он плох? Дело в том, что если несколько потоков попытаются одновременно вызвать функцию GetObject, конструктор класса CObject может быть вызван более одного раза, так как компилятор (возможно, это его ошибка) не генерирует безопасный код проверки с использованием ассемблерной инструкции cmpxchg. Хотя вероятность возникновения такой ситуации довольно низка, я рекомендую все же не игнорировать ее. Самое простое решение проблемы заключается в использовании недорогого ресурса критической секции, например, так:
CProxy& CObject::GetObject(int level)
{
::EnterCriticalSection(&g_cs);
static CObject obj;
::LeaveCriticalSection(&g_cs);
return obj.BeginTran(level);
}
Однако встает вопрос: где ее инициализировать? Можно в конструкторе глобального объекта, но если у нас будет такой же глобальный клиент, мы не сможем гарантировать, что инициализация критической секции произойдет раньше вызова функции GetObject. Нам нужно что-то, что создается, инициализируется и захватывает ресурс непосредственно в функции GetObject. В качестве этого «чего-то» я выбрал объект исполнительной системы «Мьютекс». Его использование вы и можете наблюдать в первоначальном коде.
Теперь рассмотрим пример с использованием этих классов, который сразу вскрывает первую проблему.
unsigned __stdcall thread_proc(void*)
{
// Начало транзакции
CProxy& prx = CObject::GetObject();
prx.value = 20;
prx.Commit();
return 0;
}
int main(int argc, char* argv[])
{
// Начало транзакции
CProxy& prx = CObject::GetObject();
prx.value = 10;
// Начало новой сессии
_beginthreadex(0,0,thread_proc,0,0,0);
// Эмулируем работу
// Sleep(1000);
printf("%dn",prx.value);
prx.Commit();
return 0;
}
Здесь я в двух параллельных потоках изменяю значение переменной value объекта CObject: в одном – на 10, во втором – на 20. Что выведется на консоль? Определенно сказать нельзя: если раскомментировать строчку Sleep(1000), выведется 20. С закомментированной строчкой выводится 10. Эта проблема носит название «проблема потери последнего изменения» (lost update problem) или проблема «грязной» записи. Она заключается в том, что при одновременном выполнении транзакций, в которых производится изменение данных, невозможно сказать заранее, какое конечное значение примут данные после фиксирования обеих транзакций. В случае «грязной» записи только одна из всех параллельно выполняющихся транзакций будет работать с действительными данными, остальные – нет. Другими словами, хотя данные и будут находиться в согласованном состоянии, логическая их целостность будет нарушена.
Для того чтобы наш объект удовлетворял первому уровню изоляции транзакций, на котором запрещается «загрязнение» данных, перепишем его следующим образом (изменения касаются только класса CObject):
class CObject
{
friend class CProxy;
public:
enum {READ_UNCOMMITTED};
static CProxy& GetObject(int level = -1);
~CObject()
{
DeleteCriticalSection(&exclusive);
if (hMutex) CloseHandle(hMutex);
}
protected:
CProxy& BeginTran(int level)
{
return *(new CProxy(this,level));
}
void RequestExclusive(int level)
{
if (level >= READ_UNCOMMITTED)
TestExclusive();
}
void RequestShared(int level)
{
}
void RemoveShared(int level)
{
}
void RemoveLocks()
{
RemoveAllLocks();
}
private:
CObject()
{
value = 0;
InitializeCriticalSection(&exclusive);
}
void TestExclusive()
{
//Проверка на монопольную блокировку
EnterCriticalSection(&exclusive);
//Вошли больше одного раза
if (exclusive.RecursionCount > 1)
LeaveCriticalSection(&exclusive);
}
void RemoveAllLocks()
{
//Если была установлена монопольная блокировка - снимаем
if (exclusive.OwningThread == (HANDLE)GetCurrentThreadId())
LeaveCriticalSection(&exclusive);
}
int value;
CRITICAL_SECTION exclusive;
static HANDLE hMutex;
};
Добавленный код выделен. Хочется пояснить одну деталь: так как изменять значение переменной value можно несколько раз, а Commit (или Rollback) вызывать только раз, налицо тот факт, что функция EnterCriticalSection вызывается бОльшее количество раз, нежели LeaveCriticalSection. Это ошибка – в соответствии с документацией количество вызовов функций EnterCriticalSection и LeaveCriticalSection должно совпадать. Поэтому после входа в критическую секцию я проверяю поле RecursionCount, которое устанавливает количество повторных входов потока в критическую секцию.
ПРИМЕЧАНИЕ
При работе под ОС семейства Windows 9x это поле не используется и всегда содержит 0, так что приводимый здесь и далее код будет работать только на операционных системах семейства NT.
Теперь можно определенно сказать, что выведется на консоль в следующем примере:
unsigned __stdcall thread_proc(void*)
{
// Начало второй транзакции
CProxy& prx = CObject::GetObject(CObject::READ_UNCOMMITTED);
// Здесь поток будет ожидать примерно 1 сек. До тех пор, пока
// в главном потоке не будет выполнена строчка prx.Commit();
prx.value = 20;
prx.Commit();
return 0;
}
int main(int argc, char* argv[])
{
//Начало транзакции с 0 уровнем изоляции
CProxy& prx = CObject::GetObject(CObject::READ_UNCOMMITTED);
//Изменение данных
prx.value = 10;
//Открываем новую сессию
_beginthreadex(0,0,thread_proc,0,0,0);
//Print CObject::value variable
printf("%dn",prx.value);
prx.Commit();
return 0;
}
На экран будет выведено число 10, а второй поток изменит данные только после фиксирования транзакции в главном потоке.
Хорошо, мы избавились от проблемы последнего обновления, но взгляните на следующий код:
unsigned __stdcall thread_proc(void*)
{
CProxy& prx = CObject::GetObject(CObject::READ_UNCOMMITTED);
prx.value = 20;
// Эмулируем работу
Sleep(1000);
prx.value = 40;
prx.Commit();
// Закрытие сессии
return 0;
}
int main(int argc, char* argv[])
{
// Открытие сессии
_beginthreadex(0,0,thread_proc,0,0,0);
// Эмулируем работу
Sleep(100);
CProxy& fake = CObject::GetObject(CObject::READ_UNCOMMITTED);
// В этой строчке происходит чтение «грязных данных»
// fake.get_Value() возвращает 20
int* pAr = new int[fake.get_Value()];
// Эмулируем работу
Sleep(1000);
// fake.value = 40
for(int i = 0;i < fake.value;i++)
pAr[i] = 0;
if (pAr) delete[] pAr;
fake.Commit();
return 0;
}
Если откомпилировать и запустить этот код, он гарантированно приведет к ошибке во время исполнения, так как будет осуществлен выход за границу массива в цикле. Почему? Потому что при создании массива используется значение незафиксированных данных, а в цикле – зафиксированных. Эта проблема известна как проблема «грязного чтения». Она возникает, когда одна транзакция пытается прочитать данные, с которыми работает другая параллельная транзакция. В таком случае временные, неподтвержденные данные могут не удовлетворять ограничениям целостности или правилам. И, хотя к моменту фиксации транзакции они могут быть приведены в «порядок», другая транзакция уже может воспользоваться этими неверными данными, что приведет к нарушению ее работы.
Для решения этой проблемы вводится новый уровень изоляции, на котором запрещается «грязное» чтение. Вот такие изменения нужно внести в реализацию классов CProxy и CObject для того, чтобы программа удовлетворяла второму уровню изоляции:
class CObject
{
friend class CProxy;
public:
enum {READ_UNCOMMITTED,READ_COMMITTED};
static CProxy& GetObject(int level = -1);
~CObject()
{
DeleteCriticalSection(&exclusive);
if (hShared) CloseHandle(hShared);
if (hMutex) CloseHandle(hMutex);
}
protected:
CProxy& BeginTran(int level)
{
return *(new CProxy(this,level));
}
void RequestExclusive(int level)
{
if (level >= READ_UNCOMMITTED)
TestExclusive();
}
void RequestShared(int level)
{
if (level > READ_UNCOMMITTED)
TestShared(level);
}
void RemoveShared(int level)
{
if (level == READ_COMMITTED){
RemoveSharedLock();
}
}
void RemoveLocks()
{
RemoveAllLocks();
}
private:
CObject()
{
value = 0;
InitializeCriticalSection(&exclusive);
hShared = CreateEvent(NULL,FALSE,TRUE,NULL);
}
void TestShared(int level)
{
//Проверка на монопольную блокировку
EnterCriticalSection(&exclusive);
//Устанавливаем разделяемую блокировку
//только если не была установлена монопольная блокировка
if (exclusive.RecursionCount == 1)
ResetEvent(hShared);
//Снимаем монопольную блокировку
LeaveCriticalSection(&exclusive);
}
void TestExclusive()
{
//Проверка на разделяемую блокировку
WaitForSingleObject(hShared,INFINITE);
// Проверка на монопольную блокировку
EnterCriticalSection(&exclusive);
// Вошли больше одного раза
if (exclusive.RecursionCount > 1)
LeaveCriticalSection(&exclusive);
}
void RemoveSharedLock()
{
SetEvent(hShared);
}
void RemoveAllLocks()
{
RemoveSharedLock();
// Если была установлена монопольная блокировка - снимаем
if (exclusive.OwningThread == (HANDLE)GetCurrentThreadId())
LeaveCriticalSection(&exclusive);
}
int value;
CRITICAL_SECTION exclusive;
HANDLE hShared;
static HANDLE hMutex;
};
Теперь, если изменить константу READ_UNCOMMITTED в предыдущем примере на READ_COMMITTED в качестве параметра GetObject, все станет на свои места. При инициализации массива главный поток перейдет в состояние ожидания до тех пор, пока второй поток не выполнит строчку prx.Commit(); Размер массива в главном потоке будет равен 40 элементам.
Хорошо, прекрасно! Где там следующий уровень? :) Чтобы понять, зачем нужен следующий уровень изоляции транзакций «повторяющееся чтение», рассмотрим такой пример:
unsigned __stdcall thread_proc(void*)
{
{
// Начало транзакции
CProxy& prx = CObject::GetObject(CObject::READ_COMMITTED);
prx.value = 20;
prx.Commit();
}
// Эмулируем работу
Sleep(500);
{
// Начало транзакции
CProxy& prx = CObject::GetObject(CObject::READ_COMMITTED);
prx.value = 40;
prx.Commit();
}
return 0;
}
int main(int argc, char* argv[])
{
// Начало сессии
_beginthreadex(0,0,thread_proc,0,0,0);
// Эмулируем работу
Sleep(100);
CProxy& fake = CObject::GetObject(CObject::READ_COMMITTED);
// Создание массива
int* pAr = new int[fake.get_Value()];
// Эмулируем работу
Sleep(1000);
// Инициализация массива
for(int i = 0;i < fake.value;i++)
pAr[i] = 0;
if (pAr) delete[] pAr;
fake.Commit();
return 0;
}
Если запустить этот пример, он, как и предыдущий, приведет к ошибке доступа к памяти. Дело в том, что изначально создается массив размером в 20 элементов, а в цикле инициализации используется значение 40, и на 21 элементе мы получим ошибку доступа.
Проблема повторного чтения состоит в том, что между операциями чтения в одной транзакции другие транзакции могут беспрепятственно вносить любые изменения, так что повторное чтение тех же данные приведет к другому результату.
Для поддержки третьего уровня изоляции в код изменений вносить не надо! :) Необходимо лишь не снимать разделяемые блокировки до конца транзакции. Так как метод, приведенный ниже, снимает блокировку только на уровне READ_COMMITTED:
void RemoveShared(int level)
{
if (level == READ_COMMITTED){
RemoveSharedLock();
}
}
нам нужно лишь добавить новую константу в перечисление типов блокировок.
enum {READ_UNCOMMITTED,READ_COMMITTED,REPEATABLE_READ};
Теперь, если в приведенном выше примере изменить константу READ_COMMITTED на REPEATABLE_READ в качестве параметра GetObject, код заработает правильно и без ошибок.
ПРИМЕЧАНИЕ
Совершенно не обязательно менять уровень изоляции транзакций в потоке thread_proc, работа примера не изменится, даже если изменить уровень изоляции на READ_UNCOMMITTED.
Здесь мы ставим блокировку обновления, если транзакция читает данные с уровнем изоляции REPEATABLE_READ.
В заключение, перед тем как привести полностью код с поддержкой первых трех уровней изоляции, давайте поговорим вот о чем. Созданный код реализует блокирующую модель, которая характерна для СУБД MS SQL Server 2000. Существует также версионная модель реализации блокировок, которую поддерживает такая известная СУБД, как Oracle. Чем отличаются эти модели? Рассмотрим такой код:
unsigned __stdcall thread_proc(void*)
{
// Print CObject::value variable
CProxy& fake = CObject::GetObject();
printf("in second session: %dn",fake.value);
fake.Commit();
return 0;
}
int main(int argc, char* argv[])
{
// Начало транзакции
CProxy& prx = CObject::GetObject();
prx.value = 10;
// Начало новой сессии
_beginthreadex(0,0,thread_proc,0,0,0);
// Эмулируем работу
Sleep(100);
printf("in primary session: %dn",prx.value);
prx.Commit();
return 0;
}
Здесь во второй сессии (выполняемой в отдельном потоке) мы просто читаем данные и выводим их на консоль. Так как значение переменной value мы изменили перед стартом второй сессии, совершенно очевидно, что на экран будет выведено
in second session: 10
in primary session: 10
Однако при использовании версионной модели мы должны получить
in second session: 0
in primary session: 10
Причина в том, что для каждой транзакции хранится своя копия данных (snap-shot), которая синхронизируется с основными данными только в момент фиксирования транзакции.
ПРИМЕЧАНИЕ
Oracle хранит эти копии данных в специальном хранилище, который называется rollback segment.
Версионная модель характеризуется тем, что в ней отсутствует нулевой уровень изоляции транзакций (READ UNCOMMITTED), и вместо него вводится новый уровень, который в приведенном далее коде я назвал SNAP_SHOT. Он отличается от стандартного тем, что позволяет читать действительные зафиксированные данные, даже при наличии незавершенных транзакций обновления.
Вот конечный вариант классов CProxy и CObject, который реализует обе модели и, вдобавок к этому, поддерживает два «хинта»: UPDLOCK и XLOCK. Они предназначены для изменения уровня изоляции непосредственно при работе со значением переменной, а их смысл я поясню в следующих разделах.
#define MSSQL
// #define ORACLE
class CObject;
class CProxy
{
friend class CObject;
public:
__declspec(property(get=get_Value,put=put_Value)) int value;
int get_Value(int level = -1) const;
void put_Value(int i);
void Commit();
void Rollback();
private:
int _level;
int _value;
bool fUpd;
CProxy(CObject* par,int level)
{
fUpd = false;
parent = par;
_level = level;
}
CObject* parent;
};
class CObject
{
friend class CProxy;
public:
enum {
#ifdef MSSQL
READ_UNCOMMITTED,
#elif defined ORACLE
SNAP_SHOT,
#endif
READ_COMMITTED,REPEATABLE_READ,UPDLOCK,XLOCK};
static CProxy& GetObject(int level = -1);
~CObject()
{
DeleteCriticalSection(&exclusive);
DeleteCriticalSection(&update);
if (hShared) CloseHandle(hShared);
if (hMutex) CloseHandle(hMutex);
}
protected:
CProxy& BeginTran(int level)
{
return *(new CProxy(this,level));
}
void RequestExclusive(int level)
{
ATLASSERT(level <= REPEATABLE_READ);
#ifdef MSSQL
if (level >= READ_UNCOMMITTED)
#elif defined ORACLE
if (level >= SNAP_SHOT)
#endif
TestExclusive();
}
void RequestShared(int level)
{
#ifdef MSSQL
if (level > READ_UNCOMMITTED)
#elif defined ORACLE
if (level > SNAP_SHOT)
#endif
TestShared(level);
}
void RemoveShared(int level)
{
if (level == READ_COMMITTED){
RemoveSharedLock();
}
}
void RemoveLocks()
{
RemoveAllLocks();
}
private:
CObject()
{
value = 0;
InitializeCriticalSection(&update);
InitializeCriticalSection(&exclusive);
hShared = CreateEvent(NULL,FALSE,TRUE,NULL);
}
void TestShared(int level)
{
// Проверка на монопольную блокировку
EnterCriticalSection(&exclusive);
// Устанавливаем блокировку обновления
if (level == UPDLOCK){
EnterCriticalSection(&update);
// Вошли больше одного раза
if (update.RecursionCount > 1)
LeaveCriticalSection(&update);
}
else if (level != XLOCK){
// Устанавливаем разделяемую блокировку
// только если не была установлена блокировка обновления или
// монопольная блокировка
if (update.OwningThread != (HANDLE)GetCurrentThreadId() &&
exclusive.RecursionCount == 1)
ResetEvent(hShared);
// Снимаем монопольную блокировку
LeaveCriticalSection(&exclusive);
}
// Если указан XLOCK монопольная блокировка остается
}
void TestExclusive()
{
// Проверка на разделяемую блокировку
WaitForSingleObject(hShared,INFINITE);
// Проверка на блокировку обновления
EnterCriticalSection(&update);
// Проверка на монопольную блокировку
EnterCriticalSection(&exclusive);
// Снимаем блокировку обновления
LeaveCriticalSection(&update);
// Вошли больше одного раза
if (exclusive.RecursionCount > 1)
LeaveCriticalSection(&exclusive);
}
void RemoveSharedLock()
{
SetEvent(hShared);
}
void RemoveAllLocks()
{
RemoveSharedLock();
// Если была установлена блокировка обновления - снимаем
if (update.OwningThread == (HANDLE)GetCurrentThreadId())
LeaveCriticalSection(&update);
// Если была установлена монопольная блокировка - снимаем
if (exclusive.OwningThread == (HANDLE)GetCurrentThreadId())
LeaveCriticalSection(&exclusive);
}
int value;
CRITICAL_SECTION update;
CRITICAL_SECTION exclusive;
HANDLE hShared;
static HANDLE hMutex;
};
__declspec(selectany) HANDLE CObject::hMutex = NULL;
CProxy& CObject::GetObject(int level)
{
HANDLE hLocMutex = CreateMutex(NULL,TRUE,_T("Guard-Lock-Mutex"));
bool flg = GetLastError() == ERROR_ALREADY_EXISTS;
if (flg) WaitForSingleObject(hLocMutex,INFINITE);
else CObject::hMutex = hLocMutex;
static CObject obj;
ReleaseMutex(hLocMutex);
if (flg) CloseHandle(hLocMutex);
return obj.BeginTran(level);
}
void CProxy::Commit()
{
#ifdef ORACLE
parent->value = _value;
#endif
parent->RemoveLocks();
delete this;
}
void CProxy::Rollback()
{
#ifdef MSSQL
if (fUpd)
parent->value = _value;
#endif
parent->RemoveLocks();
delete this;
}
void CProxy::put_Value(int i)
{
parent->RequestExclusive(_level);
#ifdef MSSQL
if (!fUpd)
_value = parent->value;
parent->value = i;
#elif defined ORACLE
_value = i;
#endif
fUpd = true;
}
int CProxy::get_Value(int level) const
{
if (level == -1)
level = _level;
parent->RequestShared(level);
#ifdef MSSQL
int v = parent->value;
parent->RemoveShared(level);
return v;
#elif defined ORACLE
return _value;
#endif
}
Из этих примеров должно быть понятно, что блокировки – дело серьезное. :) Но, прежде чем перейти к рассмотрению их реализации в MS SQL Server 2000, я приведу обещанные в начале уровни определения изоляции транзакций. Каждый уровень включает в себя предыдущий с предъявлением более жестких требований к изоляции.
No trashing of data (запрещение «загрязнения» данных). Запрещается изменение одних их тех же данных двумя и более параллельными транзакциями. Изменять данные может только одна транзакция, если какая-то другая транзакция попытается сделать это, она должна быть заблокирована до окончания работы первой транзакции.
No dirty read (запрещение «грязного» чтения). Если данная транзакция изменяет данные, другим транзакциям запрещается читать эти данные до тех пор, пока первая транзакция не завершится.
No nonrepeatable read (запрещение неповторяемого чтения). Если данная транзакция читает данные, запрещается изменять эти данные до тех пор, пока первая транзакция не завершит работу. При этом другие транзакции могут получать доступ на чтение данных.
No phantom (запрещение фантомов). Если данная транзакция производит выборку данных, соответствующих какому-либо логическому условию, другие транзакции не могут ни изменять эти данные, ни вставлять новые данные, которые удовлетворяют тому же логическому условию.
Если вы не совсем поняли суть последнего уровня изоляции, не расстраивайтесь. Я специально оставил его на потом, так как в данный момент нет возможности рассмотреть примеры, описывающие проблему и механизмы ее избежания.
В таблице 1 подводится итог этого раздела и изучения уровней изоляции.
Уровни изоляции
Загрязнение данных
Грязное чтение
Неповторяемое чтение
Фантомы
READ UNCOMMITTED
-
+
+
+
READ COMMITTED
-
-
+
+
REPEATABLE READ
-
-
-
+
SERIALIZABLE
-
-
-
-
Блокировки
Блокировки в MS SQL Server 2000 (в дальнейшем просто сервер) – это механизм реализации требования изолированности транзакций. Вся последующая информация специфична только для указанного сервера.
Существует три основных типа блокировок и множество специфичных. Сервер устанавливает блокировки автоматически в зависимости от текущего уровня изоляции транзакции, однако при желании вы можете изменить тип с помощью специальных подсказок – хинтов.
При открытии новой сессии по умолчанию выбирается уровень изоляции READ COMMITTED. Вы можете изменить этот уровень для данного соединения с помощью команды:
SET TRANSACTION ISOLATION LEVEL
Более подробно эту команду и хинты для операторов T-SQL мы рассмотрим в следующем разделе. Пока же я хочу подробно остановиться на типах блокировок.
Блокировки применяются для защиты совместно используемых ресурсов сервера. В качестве объектов блокировок могут выступать следующие сущности:
База данных (обозначается DB). При наложении блокировки на базу данных блокируются все входящие в нее таблицы.
Таблица (обозначается TAB). При наложении блокировки на таблицу блокируются все экстенты данной таблицы, а также все ее индексы.
ПРИМЕЧАНИЕ
Экстент – это группа из 8 страниц.
Страница – минимальная единица хранения данных в файле базы данных. Размер страницы составляет 8 Кб.
Экстент (обозначается EXT). При наложении блокировки на экстент блокируются все страницы, входящие в данный экстент.
Страница (обозначается PAG). При наложении блокировки на страницу блокируются все строки данной страницы.
Строка (обозначается RID).
Диапазон индекса (обозначается KEY). Блокируются данные, соответствующие диапазону индекса, на обновление, вставку и удаление.
SQL Server сам выбирает наиболее оптимальный объект для блокировки, однако пользователь может изменить это поведение с помощью тех же хинтов. При автоматическом определении объекта блокировки сервер должен выбрать наиболее подходящий с точки зрения производительности и параллельной работы пользователей. Чем меньше детализация блокировки (строка – самая высокая степень детализации), тем ниже ее стоимость, но ниже и возможность параллельной работы пользователей. Если выбирать минимальную степень детализации, запросы на выборку и обновление данных будут исполняться очень быстро, но другие пользователи при этом должны будут ожидать завершения транзакции. Степень параллелизма можно увеличить путем повышения уровня детализации, однако блокировка – вполне конкретный ресурс SQL Server’а, для ее создания, поддержания и удаления требуется время и память.
ПРИМЕЧАНИЕ
Блокировка занимает 96 байт. [1] Общее количество блокировок может варьироваться от 5000 до 2 147 483 647. Конкретное значение можно задать с помощью хранимой процедуры sp_configure с параметром locks.
SQL Server может принимать решение об уменьшении степени детализации, когда количество блокированных ресурсов увеличивается. Этот процесс называется эскалацией блокировок.
Вообще говоря, существует два метода управления конкуренцией для обеспечения параллельной работы множества пользователей – оптимистический и пессимистический. SQL Server использует оптимистическую конкуренцию только при использовании курсоров (cursors). Для обычных запросов на выборку и обновление используется пессимистическая конкуренция. Рассмотрим подробнее, что они собой представляют:
Оптимистический метод управления характеризуется тем, что вместо непосредственного чтения данных берется значение из буфере. Никаких блокировок при этом не накладывается. Другие транзакции могут спокойно читать или даже изменять данные. В момент фиксирования транзакции система сравнивает предыдущее (заранее сохраненное) значение данных с текущим. Если они совпадают, выполняются операции блокировки, обновления и разблокировки данных. Если же значения отличаются, то система генерирует ошибку и откатывает транзакцию. Хотя такой подход не удовлетворяет требованиям стандарта, он позволяет в определенных случаях добиться лучшей производительности, чем пессимистический подход. Достоинства этого режима очевидны: система не теряет времени на установку блокировок и ресурсов для их создания. Однако для систем с большим количеством пользователей, часто изменяющих данные, такой режим использовать не рекомендуется, так как цена отката транзакции и ее повторного выполнения значительно выше установки блокировки при чтении данных.
Пессимистический метод. В этом случае сервер всегда блокирует ресурсы в соответствии с текущим уровнем изоляции. В примере предыдущего раздела использовался именно этот метод управления конкуренцией, однако совсем не сложно адаптировать его для поддержки оптимистического режима.
Блокировки – чрезвычайно важный и неотъемлемый механизм функционирования сервера. Они применяются для каждого запроса на чтение или обновления данных, а также во многих других случаях (например, при создании новой сессии). Работой с блокировками занимается специальный модуль SQL Server’а – менеджер блокировок (Lock Manager). В его задачи входит:
создание и установка блокировок;
снятие блокировок;
эскалация блокировок;
определение совместимости блокировок;
устранение взаимоблокировок (deadlocks) и многое другое.
Когда пользователь делает запрос на обновление или чтение данных, менеджер транзакций передает управление менеджеру блокировок для того, чтобы выяснить были ли блокированы запрашиваемые ресурсы, и, если да, совместима ли запрашиваемая блокировка с текущей. Если блокировки несовместимы, выполнение текущей транзакции откладывается до тех пор, пока данные не будут разблокированы. Как только данные становятся доступны, менеджер блокировок накладывает запрашиваемую блокировку, и возвращает управление менеджеру транзакций.
Момент снятия блокировки сильно зависит от текущего уровня изоляции и типа запроса. Например, при выполнении запроса на выборку данных при уровне изоляции ниже REPEATABLE READ менеджер блокировок снимает блокировку сразу же после извлечения всех данных с текущей страницы. При этом происходит установка блокировки на следующую страницу.
Простые блокировки
SQL Server поддерживает три основных типа блокировок:
Разделяемая блокировка (Shared Lock), обозначается латинской буквой S. Эта самый распространенный тип блокировки, который используется при выполнении операции чтения данных. Гарантируется что данные, на которые она наложена, не будут изменены другой транзакцией. Однако чтение данных возможно.
Монопольная блокировка (Exclusive Lock), обозначается латинской буквой X. Этот тип применяется при изменении данных. Если на ресурс установлена монопольная блокировка, гарантируется, что другие транзакции не могут не только изменять данные, но даже читать их.
Блокировка обновления (Update Lock), обозначается латинской буквой U. Эта блокировка является промежуточной между разделяемой и монопольной блокировкой. Так как монопольная блокировка не совместима ни с одним видом других блокировок (есть одно исключение, о котором попозже), ее установка приводит к полному блокированию ресурса. Если транзакция хочет обновить данные в какой-то ближайший момент времени, но не сейчас, и, когда этот момент придет, не хочет ожидать другой транзакции, она может запросить блокировку обновления. В этом случае другим транзакциям разрешается устанавливать разделяемые блокировки, но не позволяет устанавливать монопольные. Другими словами, если данная транзакция установила на ресурс блокировку обновления, никакая другая транзакция не сможет получить на этот же ресурс монопольную блокировку или блокировку обновления до тех пор, пока установившая блокировку транзакция не будет завершена.
Прежде чем идти дальше, давайте рассмотрим небольшой пример. Для просмотра текущих блокировок существует системная хранимая функция sp_lock. Она возвращает информацию о блокировках в формате, описанном в таблице 2.
Имя колонки
Описание
spid
Идентификатор процесса SQL Server.
dbid
Идентификатор базы данных.
ObjId
Идентификатор объекта, на который установлена блокировка.
IndId
Идентификатор индекса.
Type
Тип объекта. Может принимать значения: DB, EXT, TAB, PAG, RID, KEY.
Resource
Содержимое колонки syslocksinfo.restext. Обычно это идентификатор строки (для типа RID) или идентификатор страницы (для типа PAG).
Mode
Тип блокировки. Может принимать значения: Sch-S, Sch-M, S, U, X, IS, IU, IX, SIU, SIX, UIX, BU, RangeS-S, RangeS-U, RangeIn-Null, RangeIn-S, RangeIn-U, RangeIn-X, RangeX-S, RangeX-U, RangeX-X. Об этих значениях будет сказано ниже.
Status
Статус процесса SQL Server. Может принимать значения: GRANT, WAIT, CNVRT.
Эта процедура возвращает данные о блокировках из системной таблицы syslockinfo, которая находится в базе данных master.
ПРИМЕЧАНИЕ
Информация именно из этой таблицы используется менеджером блокировок для определения совместимости блокировок при запросе ресурсов транзакциями.
Во всех примерах используется таблица test, которая создается следующим скриптом:
create table test(i int, n varchar(20))
insert into test values(1,’alex’)
insert into test values(2,’rosa’)
insert into test values(3,’dima’)
Во-первых, давайте действительно убедимся, что при чтении данных с уровнем изоляции ниже REPEATABLE READ разделяемые блокировки снимаются сразу же после извлечения данных:
print @@spid
begin tran select * from test
Мы начали транзакцию, но оставили ее открытой. Для того чтобы посмотреть, какие блокировки наложены предыдущим скриптом, вызовем процедуру sp_lock (в другой сессии) с параметром, выведенным print @@spid (у меня это 54).
СОВЕТ
Текущее значение идентификатора процесса сервера можно увидеть в строке состояния программы Query Analizer.
sp_lock 54
Результат приведен в таблице 3.
spdi
dbid
O