Проблема повышения уровня блокировок SQL Server
Если это все на одном листе (как подсказывает код), то ваша последняя проблема - ваша проблема. Вы должны пересчитывать его каждый раз, когда вставляете новую строку.
Sub Transfer()
Application.ScreenUpdating = False
Dim lastrow As Long, lngRows
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim rowCount As Long
Set wsSource = Worksheets("Forecasted Movement")
With wsSource
rowCount = .Cells(.Rows.Count, "A").End(xlUp).Row ' <-- modifed this line
On Error Resume Next
For i = 2 To rowCount
If .Cells(i, "BU").Value > 0 Then
lngRows = .Cells(i, "BU").Value
Range(Cells(i, 1), Cells(i, 72)).specialcells(xlCellTypeVisible).Copy
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 ' recalculate this for the next blank row
wsSource.Cells(lastrow, 1).Resize(lngRows).PasteSpecial Paste:=xlPasteValues
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
5 ответов
Может ли кто-нибудь просто объяснить, почему требуется эскалация блокировки и каковы так называемые накладные расходы на блокировку, пожалуйста?
Когда вы обновляете таблицу и блокируете строку, вам необходимо записать это почему-то факт: это строка, она была обновлена и заблокирована.
Когда вы обновляете миллион строк, вам нужно сделать это миллион раз и, следовательно, иметь место для хранения миллиона блокировок.
SQL Server хранит список блокировок в памяти, а Oracle - в табличных пространствах.
Вероятно, это связано с тем, что Oracle старый (старше меня), а SQL Server молод по сравнению с Oracle.
Хранение временных ресурсов (например, блокировок) в постоянном хранилище не столь очевидное решение с точки зрения дизайнера. Следует упомянуть только одно: вам может потребоваться запись на диск для выполнения SELECT FOR UPDATE .
Oracle ' Основные функции были разработаны в начале 80-х, когда хранение вещей в памяти было вообще неприемлемо. У них просто было , чтобы каким-то образом использовать дисковое пространство.
Если дисковое пространство все равно должно было использоваться, вам нужно было разместить блокировку где-нибудь на диске.
И где сохранить блокировку для строки, если не внутри самой строки?
Разработчики системы блокировок SQL Server, изобретая дизайн своей СУБД под названием Sybase, решили хранить временные вещи (то есть блокировки) во временном хранилище (то есть в ОЗУ).
Но дизайн Oracle таков. всегда сбалансирован: если в вашей базе данных 1 000 000 строк, тогда у вас есть место для хранения 1 000 000 блокировок, если у вас есть миллиард строк, вы можете хранить миллиард блокировок и т. д.
Дизайн SQL Server в этом смысле ошибочен, потому что ваша оперативная память и место на жестком диске могут быть несбалансированными. У вас может быть 16 МБ ОЗУ и несколько терабайт дискового пространства. И ваша память просто не может удерживать все блокировки.
Вот почему, когда счетчик блокировок достигает определенного предела, SQL Server решает увеличить количество блокировок: вместо того, чтобы сохранять блокировки, скажем, для 10 отдельных строк на странице данных (что требует 10 записей), он блокирует всю страницу данных (для чего требуется 1 запись).
Oracle, с другой стороны, при обновлении строки просто записывает блокировку прямо в страницу данных.
Вот почему Oracle блокирует строки -level.
Oracle не «управляет» блокировками в обычном смысле слова: вы не можете, скажем, получить список заблокированных страниц в Oracle.
Когда транзакции необходимо обновить строку, он просто переходит к строке и проверяет, заблокирована ли она.
Если это так, он смотрит, какая транзакция содержит блокировку (эта информация содержится в дескрипторе блокировки на странице данных), и добавляет себя в очередь уведомлений этой транзакции: когда блокирующие транзакции умирают, исходная получает уведомление и блокирует данные.
From С точки зрения параллелизма, эскалация блокировок - совершенно плохое решение: она ничего не добавляет к параллелизму. Вы можете, скажем, заблокировать строку, которую даже не трогали.
С точки зрения производительности, выполнение действий в памяти, конечно, быстрее, чем выполнение их на диске.
Но поскольку Oracle кэширует блоки данных, и фактические операции, описанные выше, в любом случае выполняются в памяти, производительность такая же или близкая к ней.
когда блокирующие транзакции умирают, исходная получает уведомление и блокирует данные.С точки зрения параллелизма, эскалация блокировок - совершенно плохое решение: она ничего не добавляет к параллелизму. Вы можете, скажем, заблокировать строку, которую даже не трогали.
С точки зрения производительности, выполнение действий в памяти, конечно, быстрее, чем выполнение их на диске.
Но поскольку Oracle кэширует блоки данных, и фактические операции, описанные выше, в любом случае выполняются в памяти, производительность такая же или близкая к ней.
когда блокирующие транзакции умирают, исходная получает уведомление и блокирует данные.С точки зрения параллелизма, эскалация блокировок - совершенно плохое решение: она ничего не добавляет к параллелизму. Вы можете, скажем, заблокировать строку, которую даже не трогали.
С точки зрения производительности, выполнение действий в памяти, конечно, быстрее, чем выполнение их на диске.
Но поскольку Oracle кэширует блоки данных, и фактические операции, описанные выше, в любом случае выполняются в памяти, производительность такая же или близкая к ней.
Если оптимизатор SQL Server оценивает / решает, что запрос «посетит» все строки в определенном диапазоне, будет более эффективно удерживать единственную блокировку для этого диапазона, чем вынуждать согласовывать много блокировок (блокировки должны быть проверены на тип). Это в дополнение к потреблению меньшего количества ресурсов блокировки (общесистемный ресурс).
Если у вас есть хорошо спроектированная схема и индексы, соответствующие вашей рабочей нагрузке запросов, которые регулярно поддерживаются, вам не следует беспокоиться об эскалации что происходит. Во многих случаях блокирующие блокировки таблицы могут быть устранены с помощью соответствующих покрывающих индексов.
ОБНОВЛЕНИЕ: покрывающий индекс для запроса означает, что поиск в кластеризованной таблице выполнять не нужно, и это снижает вероятность блокировки вставок в стол.
накладные расходы на блокировку означают, что управление одной блокировкой таблицы лучше с точки зрения производительности, чем управление множеством блокировок строк. поскольку каждая блокировка занимает некоторую память, множество блокировок строк может потреблять намного больше памяти, чем одна блокировка таблицы. поэтому эскалация блокировки идет от строки-> страница-> блокировка таблицы.
The definition of "efficient" is complex. Sometimes it is more efficient to optimize for concurrency if lots of processes can do there thing without collisions. Sometimes it is more efficient to take a temporary concurrency hit to get a single process done faster. The escalated lock will keep other processes out so THIS process can get its job done and get out of the way.
For specific info on how locks are maintained, you can see chapter 8 of Microsoft SQL Server 2005: The Storage Engine (I'm not affiliated, this is just the first internals info I came across). If you have a books24x7 account, it's on there. It shows on a >16gb memory machine there are 2^25 (33554432) slots in the lock hash table, with an upper limit of 2^31 slots.
For a given application you may very well find total throughput to be higher using only fine grained locks. As you can probably guess, it all depends on how the overhead of lock management compares to potential excessive locking.