DeadLocks & ADO

[Scuder]

Ух, сабжект - это, конечно, тема объёмная..

Но вопрос более узкий. Ситуация такая:
2 программы напряглись и сгенерировали deadlock на серваке.

SQL Server (2000), как ему и положено, через 5 секунд одну транзакцию убил. Соответственно, первая программа продолжила нормально работать, а вторая.. просто умерла. Поскольку ушла в вечное ожидание ответа от сервера.

Свойства timeout у рекордсета я не нашёл. Поэтому вопрос: как методами ADO или SQL Server'a этот момент исправить?

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

[Konst_One]

- работай через объект ADODB.Command
у него есть свойство CommandTimeout
- или через cn.Execute у коннекшена также есть такое же свойство
- все апдейты лучше делать через хранимые процедуры

[Scuder]

Konst_One, понимаешь, ADODB.Command - ну уж очень ёмко..
У меня под сотню процедур и под 1000 вызовов из разных программ. Писать для каждого параметра подобный код:

Код: Выделить всё

TsParmName = "TimeRec"
Set TADOPrm = TADOCmd.CreateParameter(TsParmName, adBSTR, adParamInput)
TADOCmd.Parameters.Append TADOPrm
TADOCmd.Parameters(TsParmName).Size = 8
TADOCmd.Parameters(TsParmName).Value = TimeRec

ну меня аж прям до тошноты пробирает..

- или через cn.Execute у коннекшена также есть такое же свойство
- все апдейты лучше делать через хранимые процедуры

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

Код: Выделить всё

cn.Execute "SP_Name ..."

[Ennor]

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

1. Не задирать без крайней надобности Isolation Level. В подавляющем большинстве случаев вполне достаточно стандартного Read Committed.
2. Чрезмерное увлечение распределенными транзакциями также способствует созданию дэдлокоопасных ситуаций.
3. Делай транзакции как можно короче. Невинный селект часто обновляемых данных в длинной транзакции может завалить к чертям весь сервак. То же самое можно сказать об обратной ситуации - апдейте часто читаемых строк.
4. Практика показывает, что наличие дэдлоков в определенной части БД - верный сигнал для пересмотра индексов соотв. таблиц. Некоторые товарищи сообщали об исчезновении проблем с этой напастью после перестройки/переделки индексов на задействованных таблицах.

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

Код: Выделить всё

Conn.CommandTimeout = 60
'...
Set Rec.ActiveConnection = Conn
Rec.Source = "..."
Rec.Open

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

[Scuder]

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

Ennor, я искренне рад это слышать, ибо весь сегодняшний день я занимался исключительно трассировкой и отловом deadlock'ов.

1. Не задирать без крайней надобности Isolation Level. В подавляющем большинстве случаев вполне достаточно стандартного Read Committed.

Этот параметр не используется ни в одной ХП.

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

Честно признаюсь, не знаю что такое "распределённая транзакция", либо не соотношу данное понятие с моими знаниями..

3. Делай транзакции как можно короче. Невинный селект часто обновляемых данных в длинной транзакции может завалить к чертям весь сервак. То же самое можно сказать об обратной ситуации - апдейте часто читаемых строк.

Уже понял. Но ведь как удобно вместо вызова, к примеру, 2-х ХП, вызвать одну, которая всё сделает сама. Я вообще за то, чтобы как можно больше логики было вынесено из кода программы. Но реально вместо удобства я получаю геморрой с дэдлоками. И действительно, прихожу к выводу, что использование SELECT & UPDATE в одной ХП просто недопустимо..

4. Практика показывает, что наличие дэдлоков в определенной части БД - верный сигнал для пересмотра индексов соотв. таблиц. Некоторые товарищи сообщали об исчезновении проблем с этой напастью после перестройки/переделки индексов на задействованных таблицах.

Ну вот как раз сегодня пересматривал все индексы. Всё настроено логично. Все уникальные ID проиндексированы, но выборка не всегда идёт по ним. Следовательно, периодически возникают тормоза в возврате рекордсета. Однако, иначе сделать я не могу.

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

Код: Выделить всё

Conn.CommandTimeout = 60
'...
Set Rec.ActiveConnection = Conn
Rec.Source = "..."
Rec.Open

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

Хм, такой вариант не приходил мне в голову. Проверю. Завтра. Но разве по умолчанию не задан CommandTimeout?

Покажи-ка код инициализации соединения, рекордсета и версию ADO.

ADO v.2.8

Код: Выделить всё

Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset

Conn.ConnectionString = "Provider=MSDATASHAPE; Data Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=False;User ID=xxx;Initial Catalog=xxx;Data Source=xxx"
Conn.Open

RS.CursorType = adOpenKeyset
RS.LockType = adLockOptimistic
RS.CursorLocation = adUseClient
RS.ActiveConnection = Conn

[Ennor]

IsolationLevel - свойство соединения. Я не помню, какое значение в ADO по дефолту, поэтому всегда выставляю

Код: Выделить всё

Conn.IsolationLevel = adReadCommitted

На стороне сервера это выставляется вот так:

Код: Выделить всё

SET TRANSACTION ISOLATION LEVEL Read Committed

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

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

Насчет индексирования - вовсе не обязательно ограничиваться при выборе кандидатов в индексы уникальными полями. В идеале, проиндексировано должно быть все, что вообще может упоминаться в разделе WHERE, и желательно - все, что в разделе SELECT. Вообще, вот тебе ссылка на эту тему, рекомендую - http://www.sql-server-performance.com , раздел "Find SQL Server Performance Tips by Category". Там много инфы, но поверь мне, она полезна. Я этот сайт в свое время проштудировал практ. весь.

Насчет CommandTimeout - да, у соединения дефолт 30 секунд. А вот насчет комманда непонятно. Более того, комманд не наследует это свойство у соединения. Я так понял, что рекордсет наследует.

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

Да, вот еще что. Это, конечно, нехорошо - такими вещами пользоваться, но в принципе у MSSQL 2000 есть пара фич, которые позволяют в грубой форме обходить эту проблему. Фича первая, некрасивая:

Код: Выделить всё

SET DEADLOCK_PRIORITY LOW

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

Код: Выделить всё

select * from payments WITH (NOLOCK)

Эффективный эквивалент режима изоляции транзакций Read Uncommitted - читает грязные данные, минуя любые блокировки, кроме Schema Modification, кажется. Крайне череповато чтением данных, наполовину измененных другим процессом, а потому, в принципе, недостоверных. Однако иногда это имеет смысл, все зависит от контекста задачи.
Я не уверен, что ты воспользуешься хотя бы одним из этих двух методов - уж очень они некошерные, на мой взгляд. Впрочем, решать тебе.

[codemaster]

Ух, сабжект - это, конечно, тема объёмная..

.

достаточно просто и доходчиво

http://www.rsdn.ru/article/db/deadlocks.xml

[Konst_One]

WITH (NOLOCK) часто пользую и вполне нормально, если например аналитическая прога вытаскивает данные для просмотра в таком режиме, а проги, где вводятся данные в этот момент активно вставляют/изменяют таблицы, которые участвуют в выборке, то это решение очень даже помогает

[codemaster]

WITH (NOLOCK) часто пользую и вполне нормально, если например аналитическая прога вытаскивает данные для просмотра в таком режиме, а проги, где вводятся данные в этот момент активно вставляют/изменяют таблицы, которые участвуют в выборке, то это решение очень даже помогает

только данные будут "грязными"
читаем не актуальными

[Konst_One]

в некоторых задачах - это даже плюс
да к тому же нужна быстрота работы