Сегодня сдавал 70-229. Там был такой вопрос

You are a database developer for a clothing retailer. The company has a database named Sales. This database contains a table named Inventory. The Inventory table contains the list of items for sale and the quantity available for each of those items. When sales information is inserted into the database, this table is updated. The stored procedure that updates the inventory table is shown below:

CREATE PROCEDURE UpdateInventory @IntID int
AS
BEGIN

DECLARE @Count int

BEGIN TRAN

SELECT @Count = Available
FROM Inventory WITH (HOLDLOCK)
WHERE InventoryID = @IntID

IF (@Count > 0)
UPDATE Inventory SET Available = @Count – 1
WHERE InventoryID = @IntID

COMMIT TRAN

END

When this procedure executes, the database server occasionally returns the following error message:

Transaction (Process ID 53) was deadlock on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

You need to prevent the error message from occurring while maintaining data integrity. What should you do?

A. Remove the table hint.
B. Change the table hint to UPDLOCK.
C. Change the table hint to REPEATABLEREAD.
D. Set the transaction isolation level to SERIALIZABLE.
E. Set the transaction isolation level to REPEATABLE READ.


Я выбрал B. Тем не менее, мне интересно кто какой ответ считает правильным и почему?

Здравствуйте LandWarrior, Вы писали:



LW>Я выбрал B. Тем не менее, мне интересно кто какой ответ считает правильным и почему?

Правильный ответ:

CREATE PROCEDURE UpdateInventory @IntID int
AS
BEGIN

begin tran

UPDATE Inventory SET Available = Available – 1
  WHERE InventoryID = @IntID and Avaliable > 0
commit tran

END

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

Здравствуйте Sinclair, Вы писали:

S>Правильный ответ:

S>

...
begin tran

UPDATE Inventory SET Available = Available – 1
WHERE InventoryID = @IntID and Avaliable > 0

commit tran
...

S>Эффект такой же, только выполняется единственное обращение к таблице, потому участвовать в дедлоке процедура не должна. Нефиг держать данные залоканными дольше, чем надо.

А зачем тогда вообще транзакция нужна?

Здравствуйте IT, Вы писали:

IT>Здравствуйте Sinclair, Вы писали:

S>>Правильный ответ:

S>>

IT>...
IT>begin tran

IT>UPDATE Inventory SET Available = Available – 1
IT>WHERE InventoryID = @IntID and Avaliable > 0

IT>commit tran
IT>...
IT>

S>>Эффект такой же, только выполняется единственное обращение к таблице, потому участвовать в дедлоке процедура не должна. Нефиг держать данные залоканными дольше, чем надо.

IT>А зачем тогда вообще транзакция нужна?
И правда, че это я. Не нужна. Это я чегой-то напрягся на тему контекста вызова — принудительно закоммитить, чтобы не держать update lock сверх необходимого.

K sozhaleniu cel voprosa byla ne v tom kak pravilno...
Cel voprosa proverit vashe znanie hintov i ih funkcii.



Здравствуйте Sinclair, Вы писали:

S>Здравствуйте IT, Вы писали:

IT>>Здравствуйте Sinclair, Вы писали:

S>>>Правильный ответ:

S>>>

IT>>...
IT>>begin tran

IT>>UPDATE Inventory SET Available = Available – 1
IT>>WHERE InventoryID = @IntID and Avaliable > 0

IT>>commit tran
IT>>...
IT>>

S>>>Эффект такой же, только выполняется единственное обращение к таблице, потому участвовать в дедлоке процедура не должна. Нефиг держать данные залоканными дольше, чем надо.

IT>>А зачем тогда вообще транзакция нужна?
S>И правда, че это я. Не нужна. Это я чегой-то напрягся на тему контекста вызова — принудительно закоммитить, чтобы не держать update lock сверх необходимого.

Здравствуйте LandWarrior, Вы писали:

LW>Сегодня сдавал 70-229. Там был такой вопрос


LW>A. Remove the table hint.
LW>B. Change the table hint to UPDLOCK.
LW>C. Change the table hint to REPEATABLEREAD.
LW>D. Set the transaction isolation level to SERIALIZABLE.
LW>E. Set the transaction isolation level to REPEATABLE READ.

Хорошо, давайте играть по правилам.

Убирать table hint нельзя, поскольку дефолтная стратегия MSSQL в том, чтобы отпускать локи как только возможно. Т.е. в промежутке между двумя стейтментами кто-то запросто может закоммитить изменения в табличку, и мы нарушим целостность (два декремента превратятся в один).
хинт RepeatableRead, как и уровень изоляции Repeatable Read, означают HOLDLOCK, так как их смысл в том, чтобы обеспечить одинаковость результатов селекта вплоть до конца транзакции. Стало быть, они нам не помогут. Уровень изоляции SERIALIZABLE, насколько я помню, самый страшный; это применение exclusive lock (update lock) во всех случаях, и их удержание до конца транзакции.

Причиной дедлока, очевидно, стала следующая ситуация:
два процесса одновременно начали выполнять нашу процедуру. Оба успели получить shared lock на область данных при селекте (скорее всего одна строка, но могла быть и страница) и попытались сапгрейдить их до exclusive lock'ов, чтобы сделать udpate. Тут-то им и стало плохо, т.к. каждый ждал другого. хинт updlock сообщит серверу о том, что мы собираемся менять данные, и приведет к выдаче exclusive lock вместо shared еще при селекте. Т.е. результат будет, в общем-то, тем же, что и в случае одиночного update (вот только чуть-чуть медленнее). Оно-то нам и надо, т.к. это не даст никому другому отобрать у нас лок, пока мы не закончим.

Так что правильно выбрал.

Здравствуйте, LandWarrior, Вы писали:

LW>Сегодня сдавал 70-229. Там был такой вопрос

LW>You are a database developer for a clothing retailer. The company has a database named Sales. This database contains a table named Inventory. The Inventory table contains the list of items for sale and the quantity available for each of those items. When sales information is inserted into the database, this table is updated. The stored procedure that updates the inventory table is shown below:

У тебя остались в электронном виде тесты?

Здравствуйте, LandWarrior, Вы писали:

LW>Сегодня сдавал 70-229. Там был такой вопрос

Ай-ай-ай!!!! А в начале теста ты подписался, что не будешь никому передавать содержание экзаменационных заданий.
Увидят твой пост на РСДН, кто-нибудь из MS, и аннулируют результаты экзамена(ов).

Здравствуйте, Sinclair, Вы писали:

IT>>А зачем тогда вообще транзакция нужна?
S>И правда, че это я. Не нужна. Это я чегой-то напрягся на тему контекста вызова — принудительно закоммитить, чтобы не держать update lock сверх необходимого.

Иногда вредно много знать, Антон