Учебная работа. Реферат: Блокировки в MS SQL Server 2000

Учебная работа. Реферат: Блокировки в MS SQL Server 2000

Блокировки в MS SQL Server 2000

Алексей Ширшов

Введение

Обычно блокировки разглядывают вместе с транзакциями. В данной статье упор делается в главном на механизм блокировок, его внутреннее устройство и внедрение в СУБД MS SQL Server 2000. Предполагается, что читатель отлично знаком с транзакциями и их качествами. Давайте вспомним кратко, какими качествами должны владеть транзакции в современных СУБД (эти требования носят заглавие ACID – Atomicity, Consistency, Isolation и Durability):

Atomicity (атомарность). Это требование состоит в том, что все данные, с которыми работает транзакция, должны быть или доказаны (commit), или отменены (rollback). Не обязано быть ситуации, когда часть конфигураций подтверждается, а часть – отменяется. Это правило автоматом производится для обычных данных.

Consistency (согласованность). Опосля выполнения транзакции все данные должны остаться в согласованном состоянии. Иными словами, транзакция или не изменит данных, и они останутся в прежнем состоянии, или модифицированные данные будут удовлетворять ограничениям целостности, правилам (rules) и иным аспектам согласованности данных.

Isolation (изолированность). Транзакции должны выполнятся автономно и независимо от остальных транзакций. При одновременном выполнении огромного количества конкурирующих друг с другом транзакций, хоть какое обновление определенной транзакции будет укрыто от других до того времени, пока эта транзакция не будет зафиксирована. Есть несколько уровней изолированности (изоляции) транзакций, которые разрешают избрать более наилучшее решение исходя из убеждений производительности и целостности данных. Главным способом реализации этих уровней и являются блокировки, о которых речь пойдет в данной нам статье.

Durability (долговечность либо устойчивость). Это требование состоит в том, что опосля доказательства конфигурации данных (фиксации транзакции) система перебегает в новое состояние и возврат к старенькому состоянию не вероятен, т.е. не вероятен откат в предшествующее состояние. Для вложенных транзакций это требование нарушается.

В данной статье рассматриваются механизмы реализации уровней изолированности транзакции. Эталоном ANSI были определены четыре уровня изоляции транзакций. 1-ый – это нулевой уровень изоляции, 2-ой – 1-ый уровень и так дальше. Эти уровни помогают решать разные задачи, которые будут рассматриваться тщательно дальше в процессе написания демонстрационной программки на С++. определения уровней будут даны в конце раздела.

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

Наша программка содержит всего два класса: 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 быть может вызван наиболее 1-го раза, потому что компилятор (может быть, это его ошибка) не генерирует неопасный код проверки с внедрением ассемблерной аннотации 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;

}

тут я в 2-ух параллельных потоках изменяю сказать недозволено: если раскомментировать строку 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;

};

Добавленный код выделен. Охото объяснить одну деталь: потому что изменять количество раз, нежели 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, а 2-ой поток изменит данные лишь опосля фиксирования транзакции в основном потоке.

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

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, все станет на свои места. При инициализации массива основной поток перейдет в состояние ожидания до того времени, пока 2-ой поток не выполнит строку 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 частей, а в цикле инициализации употребляется

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

Для поддержки третьего уровня изоляции в код конфигураций заносить не нужно! 🙂 Нужно только не снимать разделяемые блокировки до конца транзакции. Потому что способ, приведенный ниже, снимает блокировку лишь на уровне 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.

В заключение, перед тем как привести на сто процентов код с поддержкой первых 3-х уровней изоляции, давайте побеседуем вот о чем. Сделанный код реализует блокирующую модель, которая свойственна для СУБД 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;

}

тут во 2-ой сессии (выполняемой в отдельном потоке) мы просто читаем данные и выводим их на консоль. Потому что

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 (воспрещение «загрязнения» данных). Запрещается изменение одних их тех же данных 2-мя и наиболее параллельными транзакциями. Изменять данные может лишь одна транзакция, если какая-то иная транзакция попробует создать это, она обязана быть заблокирована до окончания работы первой транзакции.

No dirty read (воспрещение «грязного» чтения). Если данная транзакция изменяет данные, иным транзакциям запрещается читать эти данные до того времени, пока 1-ая транзакция не закончится.

No nonrepeatable read (воспрещение неповторяемого чтения). Если данная транзакция читает данные, запрещается изменять эти данные до того времени, пока 1-ая транзакция не окончит работу. При всем этом остальные транзакции могут получать доступ на чтение данных.

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. Конкретное

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’)

Во-1-х, давайте вправду убедимся, что при чтении данных с уровнем изоляции ниже REPEATABLE READ разделяемые блокировки снимаются сходу же опосля извлечения данных:

print @@spid

begin tran select * from test

Мы начали транзакцию, но оставили ее открытой. Для того чтоб поглядеть, какие блокировки наложены предшествующим скриптом, вызовем функцию sp_lock (в иной сессии) с параметром, выведенным print @@spid (у меня это 54).

СОВЕТ

Текущее



sp_lock 54

Итог приведен в таблице 3.

spdi
dbid
ObjId
IndId
Type
Resource
Mode
Status

54
8
0
0
DB
S
GRANT

Мы лицезреем обычную блокировку, которая создается для всякого соединения с базой данных. Никакой доборной блокировки установлено не было.

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

dbcc traceon(3604,1200)

select * from test

dbcc traceoff(3604,1200)

Флаг трассировки 3604 принуждает передавать отладочную информацию в текущее соединение конкретно клиенту, а 1200 – выводить информацию о блокировках. В итоге мы получим последующее:

Process 54 acquiring S lock on DB: 8 (class bit0 ref1) result: OK

Process 54 acquiring IS lock on TAB: 8:1993058136 [] (class bit0 ref1) result: OK

Process 54 acquiring IS lock on PAG: 8:1:31 (class bit0 ref1) result: OK

i n

———— ———————

1 alex

2 rosa

3 dima

(3 row(s) affected)

Process 54 releasing lock on PAG: 8:1:31

Process 54 releasing lock on TAB: 8:1993058136 []

Отлично. В первой сессии зафиксируйте транзакцию:

—print @@spid

—begin tran select * from test

commit

Повторный вызов sp_lock приводит к этим же результатам. Это подтверждает, что предшествующим запросом никаких блокировок не устанавливалось. сейчас попробуем наложить блокировку обновления. Делается это при помощи хинта updlock (хинты тщательно будут рассмотрены дальше):

begin tran select * from test with (updlock)

сейчас вызов sp_lock 54 дает наиболее увлекательный итог (таблица 4):

spdi
dbid
ObjId
IndId
Type
Resource
Mode
Status

54
8
0
0
DB
S
GRANT

54
8
1993058136
0
RID
1:29:2
U
GRANT

54
8
1993058136
0
RID
1:29:0
U
GRANT

54
8
1993058136
0
PAG
1:29
IU
GRANT

54
8
1993058136
0
TAB
IX
GRANT

54
8
1993058136
0
RID
1:29:1
U
GRANT

Как видно, на три строчки была наложена блокировка обновления, что значит невозможность обновления этих строк иными транзакциями. Не считая этого, были наложены еще две блокировки, которые относятся к типу блокировок намерения (intent locks) – блокировка на страничку и на таблицу. Пусть вас не смущает колонка Resource – ее содержание я обрисую позднее. Поле ObjId представляет собой идентификатор объекта, имя которого можно получить при помощи функции object_name. IndId – идентификатор индекса, в данном примере постоянно равен 0, потому что в таблице test индексов не употребляется.

Блокировки целей

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

Представим, некоторая транзакция пробует поменять какую-либо строчку в таблице test. Чтоб найти, что эту транзакцию нужно заблокировать, менеджеру транзакций (в отсутствие блокировок намерения) пришлось бы исследовать всю таблицу syslockinfo для проверки всех строк таблицы test. Чтоб избежать данной нам непризнательной работы, менеджер блокировок сходу устанавливает на страничку и таблицу блокировку намерения обновления (Intent Update) и монопольную блокировку намерения (Intent Exclusive) соответственно, и инспектирует уже лишь их. Таковым образом, блокировки целей – это хинт менеджеру блокировок, который дает значимый выигрыш в производительности при проверке сопоставимости блокировок.

Разглядим блокировки целей наиболее тщательно:

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

Монопольная блокировка целей (обозначается IX). Монопольная блокировка целей воспрещает доступ к данным какого-нибудь вида (даже на чтение) и дозволяет повысить эффективность работы при множественном обновлении данных. В отличие от обычный монопольной блокировки она совместима сама с собой. Т.е. иная транзакция также может наложить монопольную блокировку целей на те же ресурсы, что и 1-ая транзакция.

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

Чтоб уяснить работу этого типа блокировок, продолжим наш пример. Сделайте новейшую сессию (у меня ее идентификатор равен 55) и сделайте последующий скрипт:

begin tran

insert into test values(4,’other’)

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

sp_lock 54,55

Итог приведен в таблице 5:

spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

54
8
0
0
DB
S
GRANT

54
8
1993058136
0
RID
1:29:02
U
GRANT

54
8
1993058136
0
RID
1:29:00
U
GRANT

54
8
1993058136
0
PAG
1:29
IU
GRANT

54
8
1993058136
0
TAB
IX
GRANT

54
8
1993058136
0
RID
1:29:01
U
GRANT

55
8
0
0
DB
S
GRANT

55
8
1993058136
0
PAG
1:29
IX
GRANT

55
8
1993058136
0
TAB
IX
GRANT

55
8
1993058136
0
RID
1:29:03
X
GRANT

Как видно, прошлые блокировки остались (еще бы! мы же не зафиксировали транзакцию), и добавились четыре новейших: одна блокировка базы, не имеющая никакого значения, две блокировки целей (на таблицу и страничку) и монопольная блокировка на новейшую строчку (идентификатор 1:29:03).

Пока все более-менее ясно. Но если выполнить в первой сессии обновление данных, ради которых, фактически, и была наложена блокировка обновления, ничего не выйдет! При выполнении последующей команды сессия заблокируется:

—begin tran select * from test with (updlock)

update test set n = ‘other1’ where i = 1

Если поглядеть на содержимое таблицы syslockinfo при помощи хранимой процедуры sp_lock, можно узреть, что добавилась еще одна строчка (таблица 6).

spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

54
8
1993058136
0
RID
1:29:03
U
WAIT

Потому что на строчку 1:29:03 уже наложена монопольная блокировка, никакие остальные блокировки на тот же ресурс наложены быть не могут. Но каким образом наш безопасный update на первую строку привел к наложению блокировки обновления на четвертую строку? Дело в том, что при отсутствии индекса Менеджерблокировок не может найти, какой конкретно строке соответствует данное логическое условие, потому делается сканирование и установка монопольной блокировки на каждую строку таблицы. При наличии индекса SQL Server употребляет иной способ, основанный на блокировках спектра индекса. Чтоб перейти к их рассмотрению, вернитесь в сессию с идентификатором 55 и сделайте команду отката транзакции:

—begin tran insert into test values(4,’other’)

rollback

Опосля этого необходимо откатиться и в первой сессии:

—begin tran select * from test with (updlock)

—update test set n = ‘other1’ where i = 1

rollback

Блокировка спектра ключа либо индекса дозволяет заблокировать данные на базе логического значения, такового, как выражение в разделе where. Не считая всего остального, лишь этот тип блокировок дозволяет отлично решить делему чтения фантомов. Эта неувязка связана с тем, что даже при наличии уровня изоляции REPEATABLE READ вы сможете получить различное количество строк, соответственных определенному логическому условию в течение одной транзакции (к слову, логическое условие может и отсутствовать). Это может произойти из-за того, что не существует никаких ограничений на вставку новейших строк, невзирая на то, что обновление избранных строк заблокировано разделяемой блокировкой. Новейшие строчки могут содержать значения, удовлетворяющие логическому запросу в разделе where, так что при последующем запросе количество строк в результирующей выборке поменяется.

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

ПРИМЕЧАНИЕ

До выхода MS SQL Server 7.0 Менеджерблокировок поступал таковым образом (перекрыл всю таблицу) с хоть какими таблицами, работа с которыми велась с уровнем изоляции SERIALIZABLE.

Блокировки записи и спектра индекса

Существует два вида блокировки индекса: блокировка самой записи индекса (index entry) и блокировка спектра. Блокировка записи индекса эквивалентна блокировке строчки, т.е. Менеджерблокировок будет перекрыть строчку, если в таблице отсутствует индекс, либо же будет перекрыть запись индекса, которая соответствует данной строке. Так как индекс быть может построен по неуникальному полю, блокируемая запись не быть может идентифицирована лишь записью индекса, потому для вычисления локатора ресурса применяется метод хеширования идентификатора строчки (RID) и записи индекса.

Если б в нашей таблице существовал индекс по полю i, то в предшествующей таблице блокировок в колонке Resource фигурировали бы хеш-значения записей индекса, а тип ресурса был – KEY.

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

Допустим, у нас есть спектр значений от A до B. Если мы желаем прочесть какое-либо

Если С не существует, накладывается разделяемая блокировка на весь спектр (A,B), при всем этом вы не сможете вставить никакое значения A и B на С. Разъяснение: Если дозволить вставлять записи в спектр (A,B) либо изменять значения A и B на С, результаты повторного запроса могут поменяться, что приведет к возникновению фантома. Менеджерблокировок SQL Server’а, по техническим причинам, которые будут рассмотрены дальше, накладывает несколько хорошие блокировки: запрещается вставка в спектр [A,B), т.е. запрещается вставлять значения, равные А, и запрещается изменять значение B не только лишь на С, да и на хоть какое другое.

Если С существует, накладывается разделяемая блокировка на спектры (A,C] и [C,B). При всем этом запрещается вставлять значения из спектра (A,B) и изменять A и B на С. Менеджер блокировок SQL Server’а по техническим причинам накладывает хорошие блокировки: запрещается вставка в спектр [A,B), т.е. запрещается вставлять значения, равные А, и запрещается изменять значение B не только лишь на С, да и на хоть какое другое.

Если мы желаем вставить

Накладывается разделяемая блокировка на спектр [A, B]. Менеджерблокировок заместо разделяемой блокировки накладывает особый тип блокировки, предназначенный лишь для тестирования спектра индекса.

Вставляется

Накладывается монопольная блокировка на ключ C.

При удалении значения С, которое находится меж A и B, вероятны последующие ситуации:

Если Менеджерблокировок SQL Server’а по техническим причинам накладывает блокировку обновления на спектр (A,B], т.е. вы сможете поменять

Если значение С существует, накладывается разделяемая блокировка на спектр [A,B]. Потом накладывается монопольная блокировка на ключ C. SQL Server производит несколько хорошие деяния, а конкретно: он поначалу накладывает монопольную блокировку на спектр [A,C], а потом блокировку обновления на спектр [C,B].

При перекрытии спектра индекса SQL Server накладывает блокировку как на саму запись индекса, так и на спектр значений от текущего значения ключа до наиблежайшего последующего. Спектр может блокироваться лишь 2-мя методами: разделяемая блокировка и монопольная блокировка. Не считая этого, есть особый тип блокировки спектра, который применяется лишь для тестирования наличия остальных блокировок индекса и, соответственно, не совместим с ними. Не бывает блокировок спектра без блокировки записи индекса, кроме блокировки тестирования. Блокировка спектра и блокировка записи индекса соединяются воединыжды, формируя, таковым образом, новейшие типы блокировок. Разглядим их (таблица 7).

Спектр
Запись индекса
Тип
Описание

RangeS
S
RangeS_S
Разделяемая блокировка спектра, разделяемая блокировка записи индекса.

RangeS
U
RangeS_U
Разделяемая блокировка спектра, блокировка обновления записи индекса.

RangeX
X
RangeX_X
Монопольная блокировка на спектр и запись индекса.

RangeI
нет
RangeI_N
Блокировка тестирования. Применяется перед вставкой новейшего значения в спектр.

Блокировка спектра накладывается на спектр значений от текущего ключа до наиблежайшего последующего. Если последующего ключа не существует, блокировка накладывается на все значения, начиная с текущего. Потому что один спектр соответствует двум записям индекса, количество диапазонов обязано быть меньше количества избранных (либо изменяемых) строк на 1. Но для того, чтоб защитить граничные значения записей индекса (к примеру, от вставки такового же значения) накладывается еще две доп блокировки индекса, расширяющие общий спектр справа и слева. Таковым образом, при выбирании N’ого количества строк, количество блокировок спектра будет постоянно равно N+1.

В таблице 8 приведена матрица сопоставимости блокировок спектра индекса.

Тип
RangeS_S
RangeS_U
RangeX_X
RangeI_N

RangeS_S
+
+
-
-

RangeS_U
+
-
-
-

RangeX_X
-
-
-
+

RangeI_N
-
-
+
-

ПРИМЕЧАНИЕ

Блокировка RangeI_N совместима с хоть каким обычным типом блокировки (даже с монопольной блокировкой) исключая Sch-M.

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

create table test_key(i int)

insert into test_key values(3)

insert into test_key values(5)

insert into test_key values(7)

insert into test_key values(9)

create nonclustered index _i_test_key on test_key(i)

Чтоб не тужиться и не писать всякий раз begin tran, все соединения переключим в режим неявной транзакции, при котором SQL Server не делает автоматического фиксирования транзакции опосля удачного выполнения каждой команды. Новенькая транзакция начинается сходу же опосля ручного фиксирования текущей. Режимы переключаются при помощи последующей команды:

SET IMPLICIT_TRANSACTIONS OFF

Текущее состояние данной нам переменной можно найти последующим методом:

select case when (@@options & 2) = 2 then ‘IMPLICIT’ else ‘NOT IMPLICIT’ end

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

Для начала поглядим, что такое разделяемая блокировка спектра:

set implicit_transactions on

select * from test_key with (serializable)

Если на данный момент заглянуть в табличку syslockinfo либо вызвать хранимую функцию sp_lock, можно узреть 5 блокировок типа RangeS_S. одной строчки значение равно (ffffffffffff). Это гласит о том, что весь спектр чисел от -INF до +INF (где INF — бесконечности) заблокирован. Если вы попытаетесь вставить в таблицу test_key
spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

55
8
2009058193
2
KEY
(ffffffffffff)
RangeS-S
GRANT

56
8
2009058193
0
RID
1:29:04
X
GRANT

56
8
0
0
PAG
1:29
IX
GRANT

56
8
2009058193
2
PAG
1:34
IX
GRANT

56
8
2009058193
2
KEY
(ffffffffffff)
RangeIn
WAIT

56
8
2009058193
0
TAB
IX
GRANT

Потому что тип блокировки RangeIn (aka RangeI) несовместим ни с каким иным типом блокировки спектра, сессия за номером 56 блокируется. Приметный факт – само момент обновления индекса появилась неувязка сопоставимости блокировок. Чтоб удостоверится, что Page, которая тщательно описана в последующем разделе. Не считая этого видно, что установлены блокировки целей на таблицу и две странички: страничку с данными и страничку, на которой размещается индекс.

ПРИМЕЧАНИЕ

Индекс размещается на иной страничке вследствие того, что он является некластерным.

сейчас отменим транзакцию и выполним таковой код:

—set implicit_transactions on

—select * from test_key with (serializable)

delete from test_key with (serializable) where i = 4

—rollback

тут мы пытаемся удалить несуществующую строку. В согласовании с тем, что наш запрос должен выдавать постоянно один и этот же итог, Менеджерблокировок должен обеспечить, что никакие остальные транзакции не сумеют вставить какое-либо значения ключей на 4. Это получается из-за установки разделяемой блокировки обновления спектра.

spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

55
8
2009058193
2
KEY
(23005e3c905a)
RangeS-U
GRANT

55
8
2009058193
2
PAG
1:34
IU
GRANT

Если б удаляемое значения ключа составляли бы не 3 и 5, а 9 и 500? Был бы заблокирован спектр от 9 до 500. Таковы требования высшего уровня изоляции транзакций!

Чтоб еще лучше усвоить правила блокировки диапазонов, попытайтесь выполнить таковой запрос:

—set implicit_transactions on

select * from test_key with (serializable) where i = 10

—delete from test_key with (serializable) where i = 4

—rollback

Он приведет к установлению разделяемой блокировки на спектр от 9 до +INF, так что никакие операции вставки для этого спектра не сумеют быть выполнены.

Блокировки схемы данных

Крайний тип блокировок, рассматриваемых в данной нам статье, связан с командами DDL и схемой данных (Schema). Блокировка схемы (Schema lock) создана для блокирования метаданных объектов базы данных. Обычной командой конфигурации таковых данных может служить команда ALTER. Эта команда приводит к изменению системных таблиц метаданных, таковых, как syscolumns, sysobjects, sysforeignkeys и остальных. Чтоб запретить иным транзакциям обращаться к модифицируемым ресурсам и их метаданным, введены блокировки схемы. Блокировки схемы бывают всего 2-ух типов:

Блокировка стабильности схемы (Schema Stability Lock), обозначается Sch-S. Данный тип блокировки предназначен для гарантии неизменности метаданных, но не самих данных. Т.е. блокировка стабильности схемы – единственная из всех типов блокировок, совместимых с монопольной блокировкой. В главном она устанавливается при компиляции тела запроса либо хранимой процедуры, на это время запрещается заносить конфигурации в схему данных, но разрешается устанавливать хоть какой тип блокировок на сами данные, с которыми будет работать компилируемый запрос.

Блокировка конфигурации схемы (Schema Modification Lock), обозначается Sch-M. Данный тип блокировки не совместим ни с каким иным типом, ни с самим собой. Устанавливается опосля внесения конфигураций в схему данных и снимается опосля окончания транзакции.

Разглядим пример. В первой сессии сделайте последующий код:

begin tran alter table test add another_field int

В таблице 11 приведено содержимое таблицы syslockinfo для данной сессии.

spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

55
8
0
0
DB
S
GRANT

55
8
1
0
TAB
IX
GRANT

55
8
3
0
TAB
IX
GRANT

55
8
3
2
KEY
(31038b3afd68)
X
GRANT

55
8
1993058136
0
TAB
Sch-M
GRANT

55
8
1
1
KEY
(5800c7965d9d)
X
GRANT

55
8
3
1
KEY
(5b00f3711a81)
X
GRANT

55
8
0
0
DB
S
GRANT

Давайте проанализируем четыре выделенные строки:

Наложена монопольная блокировка ключа на объект с идентификатором 3. Это не что другое, как таблица syscolumns. Наиболее подробную информацию о этом ключе можно получить, выполнив таковой запрос:

select * from sysindexes where id = 3 and indid = 2

Наложена блокировка конфигурации схемы на объект с идентификатором 1993058136. Это не что другое, как таблица test.

Наложена монопольная блокировка ключа на объект с идентификатором 1. Это не что другое, как таблица sysobjects. Наиболее подробную информацию о этом ключе можно получить, выполнив таковой запрос:

select * from sysindexes where id = 1 and indid = 1

Наложена монопольная блокировка ключа c идентификатором 1 на объект с идентификатором 3. Подробную информацию о этом ключе можно получить, выполнив таковой запрос:

select * from sysindexes where id = 3 and indid = 1

Еще один тип блокировки – Bulk Update – употребляется вместе с операциями громоздкого обновления. Он совместим лишь с блокировкой стабильности схемы и с самим собой.

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

Набросок 1.

Таблица 12 указывает сопоставимость обрисованных типов блокировок друг с другом.

Тип
IS
S
U
IX
SIX
X
Sch-S
Sch-M
BU

IS
+
+
+
+
+
-
+
-
-

S
+
+
+
-
+
-
+
-
-

U
+
+
-
-
-
-
+
-
-

IX
+
+
-
+
-
-
+
-
-

SIX
+
+
-
-
-
-
+
-
-

X
-
-
-
-
-
-
+
-
-

Sch-S
+
+
+
+
+
+
+
-
+

Sch-M
-
-
-
-
-
-
-
-
-

BU
-
-
-
-
-
-
+
-
+

Хинты

Как уже говорилось ранее, для каждой сессии быть может избран применяемый по дефлоту уровень изоляции, работающий при выполнении всех установок T-SQL. Ниже я покажу, как это создать.

Разглядим синтаксис команды установки уровня изоляции по дефлоту для текущего соединения.

SET TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED

READ UNCOMMITTED – устанавливает уровень изоляции транзакций, на котором решается неувязка «грязной» записи;

READ COMMITTED – устанавливает уровень изоляции, на котором решается неувязка «грязного» чтения;

REPEATABLE READ – устанавливает уровень изоляции, на котором решается неувязка неповторяемого чтения;

SERIALIZABLE – устанавливает уровень изоляции, на котором решается неувязка чтения фантомов.

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

Существует документированный метод просмотра текущего уровня изоляции при помощи команды DBCC USEROPTIONS, но лично я постоянно пользуюсь наиболее массивным, хотя и не документированным, средством. Чтоб выяснить текущее

dbcc traceon (3604)

dbcc pss(1,53)

dbcc traceoff (3604)

Команда dbcc pss создана для вывода текущих опций обозначенного в параметре соединения. Вот ее синтаксис:

DBCC pss( suid, spid, printopt = 0 )

suid – идентификатор юзера (см. хранимую функцию sp_helpuser);

spid – идентификатор серверного процесса;

printopt – флаг вывода (0 – обычная информация, 1 – расширенная). По дефлоту 0.

Эта команда выводит много увлекательной инфы, но для нас основное – узнать текущий уровень изоляции. Для этого необходимо посреди выведенных бессчетных переменных отыскать одну с заглавием isolation_level. В моем случае она оказалась равна 0. Это гласит о том, что для данного соединения употребляется уровень изоляции по дефлоту, который, как я уже гласил, равен READ COMMITTED. Вот все значения, которые может принимать эта переменная:

0 – уровень изоляции по дефлоту (READ COMMITTED);

1 – уровень изоляции READ UNCOMMITTED;

2 – уровень изоляции READ COMMITTED;

3 – уровень изоляции REPEATABLE READ;

4 – уровень изоляции SERIALIZABLE.

сейчас разглядим, каким образом управлять уровнем изоляции транзакций на уровне отдельных установок. Вот синтаксис раздела FROM, который относится к нашей теме (все произнесенное ниже относится не только лишь к запросам, да и к командам конфигурации данных DELETE и UPDATE):

FROM { < table_source > }

<table_source> ::=

table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,…n ] ) ]

< table_hint > ::=

READUNCOMMITTED

Уровни READUNCOMMITTED и READCOMMITTED соответствуют одноименным уровням изоляции транзакций, лишь пишутся слитно. Блокировки при использовании этих уровней снимаются сходу опосля выполнения команды. В случае хинтов REPEATABLEREAD и SERIALIZABLE блокировки остаются до конца транзакции. Дальше приводится подробное описание всех других хинтов.

FASTFIRSTROW – хинт не относится к блокировкам и уровням изоляции. Дозволяет стремительно избрать 1-ые строки результирующего набора.

HOLDLOCK – аналогичен хинту SERIALIZABLE, т.е. устанавливает разделяемую блокировку спектра индекса (если индекс существует) либо ординарную разделяемую блокировку на страничку либо таблицу. Оставлен для оборотной сопоставимости.

NOLOCK – разрешается применять лишь с командой SELECT. Аналогичен хинту READUNCOMMITTED, т.е. не накладывает никаких блокировок и игнорирует блокировки остальных транзакций.

PAGLOCK – пожелание менеджеру блокировок устанавливать блокировки на уровне страничек. Это пожелание производится весьма изредка.

READPAST – разрешается применять лишь с командой SELECT. Дозволяет избрать лишь те строчки, на которые не установлена монопольная блокировка. Иными словами, дозволяет избрать все не модифицированные строчки.

ROWLOCK – пожелание менеджеру блокировок устанавливать блокировки на уровне строк. Это пожелание производится весьма изредка.

TABLOCK – дозволяет установить на всю таблицу блокировку, тип которой зависит от команды, в какой этот хинт употребляется. Для команды SELECT будет установлена разделяемая блокировка на всю таблицу до того времени, пока команда не выполнится, но не до конца транзакции. действие хинта можно продлить при помощи HOLDLOCK. к примеру:

select * from test with(tablock,holdlock)

Установит разделяемую блокировку до конца транзакции.

TABLOCKX – устанавливает монопольную блокировку на всю таблицу до конца транзакции даже для команды SELECT.

UPDLOCK – разрешается применять лишь с командой SELECT. Дозволяет установить блокировку обновления заместо разделяемой блокировки. действие блокировки длится до окончания транзакции.

XLOCK – действие аналогично хинту UPDLOCK с той только различием, что устанавливается монопольная блокировка на ресурс.

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

i
n

1
alex

2
rosa

3
dima

Начнем с монопольных блокировок. В первой сессии сделайте команду:

update test set n = ‘other’ where i = 2

К тому же результату (наложение монопольной блокировки на строчку) привела бы команда

select * from test with(xlock) where i = 2

сейчас, во 2-ой сессии:

select * from test with (nolock)

2-ая сессия не заблокировалась и выдала три строки, включая модифицированную. Если б мы не указали хинта nolock, сессия заблокировалась бы, потому что по дефлоту установлен режим изоляции READ COMMITTED. Этому режиму нужна разделяемая блокировка, но потому что монопольная блокировка не совместима ни с какой иной, Менеджерблокировок заблокировал бы сессию.

Еще один увлекательный итог можно получить при помощи последующей команды (во 2-ой сессии):

select * from test with (readpast)

Результаты ее выполнения зависят от того, какую команду мы выполнили в первой сессии. Если производился запрос с хинтом xlock, результирующий набор будет содержать все строчки таблицы. Но если была выполнена команда UPDATE, SQL Server выдаст нам всего две строки: 1 и 3. Такое каким-то образом отмечает вправду модифицированные строчки либо совершенно не заносит их в файл базы данных. Чтоб получить больше инфы по этому поводу, воспользуемся очередной недокументированной возможностью, позволяющей просматривать физическое содержимое данных.

Но поначалу я бы желал разглядеть содержимое колонки Resources, которую возвращает хранимая процедура sp_lock.

Вот синтаксис команды, при помощи которой можно получить физическое содержимое таблицы:

DBCC PAGE (dbname, filenum, pagenum [,print option] [,инфы. Может принимать значения, приведенные в таблице 14.

содержимое каждой строки.

2
Выводится заголовок странички и полный дамп памяти.

3
Относительно новейший флаг, который аналогичен флагу 1, с той различием, что значения форматируются в удобочитаемый вид для каждой колонки.

база, в какой содержится таблица test, имеет идентификатор 8):

dbcc traceon (3604)

dbcc Page(8,1,31,3,1)

dbcc traceoff (3604)

Приведу итог, выводимый данной командой:

Page: (1:31)

————

BUFFER:

——-

BUF @0x00D88500

—————

bpage = 0x19548000 bhash = 0x00000000 bpageno = (1:31)

bdbid = 8 breferences = 1 bstat = 0xb

bspin = 0 bnext = 0x00000000

Page HEADER:

————

Page @0x19548000

—————-

m_pageId = (1:31) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0

m_objId = 1993058136 m_indexId = 0 m_prevPage = (0:0)

m_nextPage = (0:0) pminlen = 8 m_slotCnt = 3

m_freeCnt = 8026 m_freeData = 247 m_reservedCnt = 0

m_lsn = (6:181:2) m_xactReserved = 0 m_xdesId = (0:0)

m_ghostRecCnt = 0 m_tornBits = 0

Allocation Status

——————

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60

——————

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

19548060: 00080030 00000001 02000002 15001100 0……………

19548070: 656c6100 78 .alex

i = 1

n = alex

Slot 1 Offset 0xe1

——————

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

195480E1: 00080030 00000002 02000002 16001100 0……………

195480F1: 68746f00 7265 .other

i = 2

n = other

Slot 2 Offset 0x8a

——————

Record Type = PRIMARY_RECORD

Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

1954808A: 00080030 00000003 02000002 15001100 0……………

1954809A: 6d696400 61 .dima

i = 3

n = dima

Отсюда можно прийти к выводу, что данные обновляются конкретно, а Менеджерблокировок следует какому-то специальному методу, проверяющему лог транзакций (transaction log), при использовании хинта readpast.

На будущее желаю увидеть, что тип странички (выделен) может принимать несколько значений, из которых для нас важны два: 1 – для страничек данных, 2 – для страничек индексов.

Давайте продвигаться далее. Потому что в данный момент у нас заблокирована одна строчка (2-ая), попытаемся во 2-ой сессии выполнить таковой запрос:

—select * from test with (readpast)

select * from test where i = 1

Ура! В конце концов-то она заблокировалась! 🙂 Это вышло поэтому, что в отсутствии индекса на таблице оптимизатор делает полное сканирование таблицы для поиска строк, удовлетворяющих данному логическому условию. При всем этом он безизбежно натыкается на заблокированную строку, что приводит к блокировке транзакции. Вот какие блокировки установлены на текущий момент (сокращенно, таблица 15):

ObjId
IndId
Type
Resource
Mode
Status

1993058136
0
PAG
1:31
IS
GRANT

1993058136
0
PAG
1:31
IX
GRANT

1993058136
0
TAB
IS
GRANT

1993058136
0
TAB
IX
GRANT

1993058136
0
RID
1:31:01
X
GRANT

1993058136
0
RID
1:31:01
S
WAIT

1993058136
0
PAG
1:31
IS
GRANT

1993058136
0
PAG
1:31
IX
GRANT

1993058136
0
TAB
IS
GRANT

1993058136
0
TAB
IX
GRANT

Мы лицезреем, что на первую строчку наложена монопольная блокировка и некий процесс (а поточнее, наша 2-ая сессия) желает получить на эту же строчку разделяемую блокировку. Чтоб решить эту делему, нужен индекс. Но до этого необходимо разобраться с текущей ситуацией.

Представим, вы не имеете доступа к сессиям 54 и 55, которые в данный момент соперничают в получении доступа к ресурсам, и не сможете выполнить команды фиксации транзакции либо отката. Единственным вероятным выходом из сложившейся ситуации является убиение 1-го из действий. Для получения подробной инфы о процессе можно пользоваться хранимой процедурой sp_who и ее недокументированным аналогом sp_who2. Крайняя возвращает существенно больше инфы, но результаты выполнения обеих содержат колонку cmd, в какой хранится тип выполняющейся команды, если процесс заблокирован, либо AWAITING COMMAND – если все нормально.

Для получения наиболее подробной инфы о выполняющейся команде можно применять последующую команду:

DBCC INPUTBUFFER (55)

Эта команда возвращает таковой итог:

EventType Parameters EventInfo

————— ———- ————————————————

Language Event 0 —SET IMPLICIT_TRANSACTIONS on

—select * from test where i = 1

Эта команда может употребляться для просмотра первых 255 знаков установок для данной сессии.

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

Но все это не про нас, вернемся к первой сессии (с номером 54) и отменим транзакцию:

—SET IMPLICIT_TRANSACTIONS on

—update test set n = ‘other’ where i = 2

rollback

2-ая сессия мгновенно разблокируется, опосля что в ней также необходимо откатить транзакцию:

—SET IMPLICIT_TRANSACTIONS on

—select * from test with (readpast)

—select * from test where i = 1

rollback

Давайте сделаем некластерный индекс и выполним те же самые деяния. Откройте новейшую сессию и сделайте последующую команду:

create nonclustered index _i_idx on test(i)

сейчас в первой сессии выполним обновление записи:

—SET IMPLICIT_TRANSACTIONS on

update test set n = ‘other’ where i = 2

—rollback

А во 2-ой – запрос:

—SET IMPLICIT_TRANSACTIONS on

—select * from test with (readpast)

select * from test with (xlock) where i = 1

—rollback

Чтоб узреть блокировки 2-ой сессии, я поставил хинт xlock. запрос не заблокируется, давайте поглядим содержимое syslockinfo (таблица 16).

spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

55
8
1993058136
0
RID
1:31:00
X
GRANT

55
8
1993058136
0
PAG
1:31
IX
GRANT

54
8
1993058136
0
PAG
1:31
IX
GRANT

55
8
1993058136
3
PAG
1:29
IX
GRANT

55
8
1993058136
0
TAB
IX
GRANT

54
8
1993058136
0
TAB
IX
GRANT

54
8
1993058136
0
RID
1:31:01
X
GRANT

55
8
1993058136
3
KEY
(21001d31a802)
X
GRANT

Как лицезреем, 1-ая сессия наложила монопольную блокировку на первую строчку 1:31:01, а 2-ая – на нулевую 1:31:00. Не считая этого, 2-ая сессия наложила монопольную блокировку на ключ (крайняя строчка) и монопольную блокировку целей на страничку, где размещается индекс (1:29).

Чтоб вправду убедиться, что индекс сотворен конкретно на данной нам страничке, необходимо выполнить еще одну недокументированную команду:

extentinfo [( dbid [, table_id [, index_id]])]

dbid – идентификатор базы данных; dbname – заглавие базы данных;

table_id – идентификатор таблицы; table_name – заглавие таблицы;

index_id – идентификатор индекса; index_name – заглавие индекса.

ПРИМЕЧАНИЕ

Для получения полного перечня установок DBCC (Database Console Commands) можно пользоваться таковой командой:

dbcc traceon (2520)

dbcc help(‘?’)

dbcc traceoff (2520)

Чтоб получить синтаксис определенной команды, к примеру, Page, сделайте такую последовательность:

dbcc traceon (2520)

dbcc help(‘Page‘)

dbcc traceoff (2520)

В нашем случае последующий набор установок покажет все странички таблицы test:

dbcc traceon (3604)

dbcc extentinfo(8,’test’)

dbcc traceoff (3604)

В колонке Page_id указаны идентификаторы страничек, а в колонке index_id – идентификаторы индекса. Просмотрим содержимое странички индекса:

dbcc traceon (3604)

dbcc Page(8,1,29,1,1)

dbcc traceoff (3604)

Вот итог работы данной команды:

Page: (1:29)

————

BUFFER:

——-

BUF @0x00DA2740

—————

bpage = 0x1A25A000 bhash = 0x00000000 bpageno = (1:29)

bdbid = 8 breferences = 1 bstat = 0x9

bspin = 0 bnext = 0x00000000

Page HEADER:

————

Page @0x1A25A000

—————-

m_pageId = (1:29) m_headerVersion = 1 m_type = 2

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4

m_objId = 1993058136 m_indexId = 3 m_prevPage = (0:0)

m_nextPage = (0:0) pminlen = 13 m_slotCnt = 3

m_freeCnt = 8042 m_freeData = 144 m_reservedCnt = 0

m_lsn = (6:181:20) m_xactReserved = 0 m_xdesId = (0:0)

m_ghostRecCnt = 0 m_tornBits = 839385609

Allocation Status

——————

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

DATA:

——

Slot 0, Offset 0x60

——————-

Record Type = INDEX_RECORD

Record Attributes = NULL_BITMAP

1A25A060: 00000116 00001f00 00000100 00000300 …………….

Slot 1, Offset 0x70

——————-

Record Type = INDEX_RECORD

Record Attributes = NULL_BITMAP

1A25A070: 00000216 00001f00 01000100 00000300 …………….

Slot 2, Offset 0x80

——————-

Record Type = INDEX_RECORD

Record Attributes = NULL_BITMAP

1A25A080: 00000316 00001f00 02000100 00000300 …………….

Потому что тип странички, обозначенный в заголовке, равен 2, данная страничка является индексной. Не считая этого,

ПРИМЕЧАНИЕ

информацию обо всех индексах данной таблицы можно получить при помощи такового запроса: select * from sysindexes where id = object_id(‘test’)

На этом я закончу разглядывать хинты SQL Server’а и блокировки и перейду к дилемме взаимоблокировок (deadlocks). Не забудьте завершать транзакции в 54 и 55 сессиях.

СОВЕТ

Для определения текущего количества незавершенных транзакций можно пользоваться функцией @@trancount.

Взаимоблокировки

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

Разглядим сущность взаимоблокировки на 2-ух транзакциях (t1 и t2), обращающихся к двум ресурсам (A и B). Представим, что транзакция t1 перекрывает ресурс A, опосля что транзакция t2 перекрывает ресурс B. Если сейчас транзакция t1 попробует получить доступ к ресурсу B, а транзакция t2 – к ресурсу А, возникнет взаимоблокировка. В таком состоянии транзакции не могут разрешить конфликт и перебегают в состояние ожидания на неопределенно длинный срок. Чтоб поменять время ожидания ресурса для текущего соединения, употребляется последующая команда:

SET LOCK_TIMEOUT timeout_period

timeout_period задается в миллисекундах. Если указывается время.

В SQL Server’е предусмотрен механизм автоматического определения взаимоблокировок и разрешения конфликтов. Для этого предназначен особый серверный процесс LOCK MONITOR, который пробуждается любые 5 секунд [2]. Он инспектирует наличие 2-ух и наиболее ожидающих транзакции и вычисляет зависимости меж ними. Если оказывается, что транзакции зависят друг от друга, т.е. для получения блокировки одной из их нужно снять блокировку иной транзакции, фиксируется факт появления взаимоблокировки. Единственный метод устранения задачи заключается в отмене одной из транзакций. Но какой? Для каждой транзакции вводится понятие цены либо приоритета. Это значение задается на уровне соединения последующей командой:

SET DEADLOCK_PRIORITY @deadlock_var

Задание наименьшего приоритета для соединения приводит к откату транзакций при появлении взаимоблокировок с кодом ошибки 1205.

Давайте разглядим обычный пример взаимоблокировки. Для большей наглядности я продемонстрирую его на одной таблице из 3-х (!) строк.

Итак, мы продолжаем работать с нашей таблицей test, на поле i которой сотворен некластерный индекс.

В первой сессии выполним таковой запрос:

SET IMPLICIT_TRANSACTIONS on

select * from test with(updlock) where i = 2

тут мы наложили блокировку обновления на вторую строчку таблицы. сейчас во 2-ой сессии введем последующий код:

SET IMPLICIT_TRANSACTIONS on

select * from test with (updlock) where i = 1

Та же блокировка обновления накладывается на первую строку. Перейдем опять к первой сессии:

—SET IMPLICIT_TRANSACTIONS on

—select * from test with(updlock) where i = 2

update test set n = (select n from test where i = 2) where i = 1

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

сейчас во 2-ой сессии введем похожую команду:

—SET IMPLICIT_TRANSACTIONS on

—select * from test with (updlock) where i = 1

update test set n = (select n from test where i = 1) where i = 2

Вот она, взаимоблокировка! Обе сессии заблокированы. Но наше ликование будет недолгим: приблизительно через 5 секунд Менеджерблокировок сообразит, что появилась взаимоблокировка и откатит первую транзакцию.

Server: Msg 1205, Level 13, State 50, Line 3

Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen

as the deadlock victim. Rerun the transaction.

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

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

Это правило помогает, но не во всех вариантах. Разглядим таковой пример. В первой сессии исполняем команду:

set implicit_transactions on

select * from test with (repeatableread)

Это приводит к наложению разделяемых блокировок на все строчки таблицы test. Во 2-ой сессии исполняем этот же запрос:

set implicit_transactions on

select * from test with (repeatableread)

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

Блокировки для первой строчки приведены в таблице 17.

spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

55
8
1993058136
0
RID
1:31:00
S
GRANT

57
8
1993058136
0
RID
1:31:00
S
GRANT

Нас интересует конкретно 1-ая строчка, потому что на данный момент мы попытаемся в первой сессии поменять данные:

—set implicit_transactions on

—select * from test with (repeatableread)

update test set n = ‘other’ where i = 1

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

spid
dbid
ObjId
IndId
Type
Resource
Mode
Status

55
8
1993058136
0
RID
1:31:00
S
GRANT

57
8
1993058136
0
RID
1:31:00
U
GRANT

57
8
1993058136
0
RID
1:31:00
X
CNVT

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

Если сейчас выполнить во 2-ой сессии ту же команду:

—set implicit_transactions on

—select * from test with (repeatableread)

update test set n = ‘other’ where i = 1

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

Чтоб избавиться от схожих ситуаций, необходимо устанавливать при запросе данных заместо разделяемой блокировки сходу блокировку обновления. В этом случае 2-ая сессия будет заблокирована на команде подборки данных до окончания транзакции в первой сессии. Но и при установке блокировки обновления недозволено быть уверенным, что не произойдет обоюдного блокирования транзакций. Если во 2-ой сессии просто выполнить запрос с уровнем изоляции REPEATABLE READ, на все строчки снова будет установлена разделяемая блокировка, и при попытке обновления записи в первой сессии, транзакция будет заблокирована. Единственным выходом из ситуации, когда происходит выполнение транзакций с различными уровнями изоляции, будет наложение монопольных блокировок на все строчки при запросе данных в первой сессии. Хотя таковой способ строго не рекомендуется, он вероятен, благодаря наличию хинта xlock. Если вносимые транзакцией конфигурации будут затрагивать наиболее 70% таблицы, есть смысл заместо xlock установить хинт tablockx, хотя Менеджерблокировок довольно умен, чтоб без помощи других выполнить эскалацию блокировок.

Заключение

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

Обладатели блокировки

Может быть, вы не думали над сиим вопросцем, либо задумывались, что обладателем всех блокировок является Менеджерблокировок, но это не так. У каждой блокировки есть обладатель, и его можно отыскать в таблице syslockinfo в поле req_ownertype. Это поле может принимать три значения: 1, 2 и 3. Если значение равно 1, обладателем блокировки является транзакция – это самая всераспространенная ситуация. Если req_ownertype равен 2 – обладателем является курсор, сделанный с опцией SCROLL_LOCKS. И, в конце концов, правило, объектом схожей блокировки является база данных, а сама блокировка накладывается в момент выполнения команды use [database] и снимается, когда производится иная команда use, либо заканчивается сессия.

Функции индекса

По дефлоту SQL Server выбирает более пригодный, по его воззрению, уровень детализации блокировок и делает эскалацию блокировок при увеличении их количества. Вы сможете задать другую стратегию блокировки конкретно в самом запросе, при помощи хинтов, а сможете (лишь для индексов) указать заблаговременно степень детализации объектов блокировок. Делается это при помощи хранимой процедуры sp_indexoption. Вот ее синтаксис:

sp_indexoption [ @IndexNamePattern = ] ‘index_name’

, [ @OptionName = ] ‘option_name’

, [ @OptionValue = ] ‘value’

IndexNamePattern – имя индекса.

OptionName – Имя функции. Может принимать значения, перечисленные в таблице 19.

Имя функции
Описание

AllowRowLocks
Если установлена в TRUE, разрешает использования блокировок на уровне строк.

AllowPageLocks
Если установлена в TRUE, разрешает использования блокировок на уровне страничек.

DisAllowRowLocks
Если установлена в TRUE, воспрещает внедрение блокировок на уровне строк.

DisAllowPageLocks
Если установлена в TRUE, воспрещает внедрение блокировок на уровне страничек.

Булево

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

set implicit_transactions on

select City from Customers with (repeatableread)

Но если перед сиим запросов вызвать хранимую функцию sp_indexoption последующим образом:

EXEC sp_indexoption ‘Customers.City’, ‘allowrowlocks’, false

Будет установлена всего одна (!) разделяемая блокировка на таблицу Customers.

Блокировки приложений

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

ПРИМЕЧАНИЕ

Может быть также написание расширенных хранимых процедур, в каких использовались бы механизмы синхронизации операционной системы. До выхода SQL Server 2000 это был единственный способ.

Разглядим синтаксис процедур.

sp_getapplock [ @Resource = ] ‘resource_name’,

[ @LockMode = ] ‘lock_mode’

[ , [ @LockOwner = ] ‘lock_owner’ ]

[ , [ @LockTimeout = ] ‘value’ ]

Resource – неповторимое имя ресурса, аналогично неповторимому имени объекта мьютекса либо действия.

LockMode – Тип запрашиваемой блокировки. Может принимать значения Shared, Update, Exclusive, IntentExclusive и IntentShared.

LockOwner – обладатель блокировки. По дефлоту – transaction. Может принимать значения transaction либо session. При указании обладателя Session, блокировка снимается или при окончании сессии, или при окончании работы SQL Server’а, или при вызове процедуры sp_releaseapplock.

LockTimeout – сходу. По дефлоту берется

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

запрос на ресурс был прерван.

-3
запрос на ресурс был прерван вследствие появления мертвой блокировки.

-999
В функцию были переданы неправильные характеристики.

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

sp_releaseapplock [ @Resource = ] ‘resource_name’

[ , [ @LockOwner = ] ‘lock_owner’ ]

Resource – неповторимое имя ресурса, аналогично неповторимому имени объекта мьютекса либо действия.

LockOwner – обладатель блокировки. По дефлоту – transaction. Может принимать значения transaction либо session.

Эта процедура обязана быть вызвана столько раз, сколько вызывалась процедура sp_getapplock.

Примеры использования данной связки имеются в MSDN.

На этом я заканчиваю статью, надеюсь, она оказалась вам увлекательной и полезной в практическом плане.

Перечень литературы

Microsoft SQL Server 2000, Е.В Мамаев, 2001 г.

Inside Microsoft SQL Server 2000, Kalen Delaney, 2001 г.

]]>