|
|
СИСТЕМЫ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ #02/96
|
|
Д. Холмс-Кинселла, А. Брюзгин
Очень важно понимать механизм установки блокировок в Oracle. Подобно SQLBase, Oracle использует блокировки в качестве механизма управления многопользовательским доступом и целостностью данных. Oracle обеспечивает целостность путем установки блокировок с целью запретить командам UPDATE конфликтовать с другими командами UPDATE на тех же данных. В SQLBase программа может определять тип блокировок путем установки уровня изоляции. В Oracle, однако, уровень изоляции задать нельзя - механизм блокировок в Oracle существенно отличается от SQLBase. Oracle использует три типа блокировок: на данные (data), словарь (dictionary) и внутренние (internal). Стандартный механизм блокировок Oracle гарантирует, что команды SELECT не блокируют команды UPDATE, поскольку для записи измененных данных перед изменением в таблице используются так называемые rollback segments. В то же время команды SELECT с предложением FOR UPDATE будут препятствовать остальным пользователям модифицировать ту же строку. Аналогично, команды SELECT никогда не блокируются командами UPDATE на той же строке (строках) данных. Однако, команда UPDATE будет ожидать окончания (commit или rollback) любой предыдущей команды UPDATE.
Блокировки на данные устанавливаются при выполнении команд манипуляции данными на языке SQL, такими как SELECT, UPDATE, INSERT и DELETE. Эти блокировки могут налагаться на двух различных уровнях: на уровне строки и таблицы.
Блокировка на уровне строки (row-level lock) налагается на одну или более строк, когда содержимое этих строк изменяется. Если транзакция получает row-level lock, на всю таблицу налагается блокировка на словарь (dictionary lock). Это препятствует выполнению операций, которые могут изменить структуру таблицы, таких как ALTER TABLE или DROP TABLE. Row-level lock может быть получен путем подачи команды UPDATE или команды SELECT с предложением FOR UPDATE.
Блокировка на уровне таблицы (table-level lock) является блокировкой данных, которая может использоваться в следующих режимах: EXCLUSIVE, SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE и SHARE ROW EXCLUSIVE. Эта блокировка может быть наложена пользователем, который желает изменить стандартную модель блокировок Oracle.
Exclusive table lock позволяет только тому пользователю, который выдал команду LOCK TABLE <имя таблицы> IN EXCLUSIVE MODE, использовать на этой таблице команды SELECT. Команды INSERT, UPDATE и DELETE запрещены. Share table lock допускает выполнение параллельных команд SELECT. Команды INSERT, UPDATE и DELETE запрещены.
Row share table lock указывает, что транзакция, обладающая блокировкой на таблицу, наложила блокировки на строки таблицы и намеревается произвести в них изменения. Это препятствует получению исключительной блокировки на таблицу (exclusive lock) другими пользователями. Row share lock допускает многопользовательский доступ к таблице. Для установки данной блокировки следует использовать команду LOCK TABLE <имя таблицы> IN ROW SHARE MODE. При этом разрешены все команды SELECT, INSERT, UPDATE и DELETE. Это дает возможность многим пользователям устанавливать блокировки row share. В то же время, попрежнему только один exclusive lock может существовать на таблице в каждый момент времени. Пока кто-то из пользователей имеет exclusive lock, другие пользователи не могут выпускать команды INSERT, UPDATE или DELETE, пока exclusive lock не будет сброшен. Блокировки на таблицу сбрасываются командами COMMIT или ROLLBACK.
Share update table lock полностью соответствует Row share table lock и существует в Oracle 7 только для совместимости с более ранними версиями. Share row exclusive table lock дает возможность одновременно выпускать несколько команд SELECT и не позволяет другим пользователям устанавливать блокировки на таблицу в режиме SHARE. Команды INSERT, UPDATE и DELETE запрещены. Блокировка Row exclusive table lock аналогична Row share table lock. Однако она также запрещает блокировки таблицы в режиме SHARE. Блокировки Row exclusive table lock автоматически накладываются при выполнении команд INSERT, UPDATE и DELETE. Для установления этой блокировки следует подать команду LOCK TABLE <имя таблицы> IN ROW EXCLUSIVE MODE. Блокировки сбрасываются командами COMMIT и ROLLBACK.
Внимание: Широкое использование режимов ROW SHARE или ROW EXCLUSIVE на таблицах, в которых большое число пользователей изменяет данные, может привести к снижению производительности и проблемам с многопользовательским доступом.
Блокировки на словарь данных накладываются на объект, который имеет data lock, с тем, чтобы защитить объект от изменений его структуры тем же самым или любым другим пользователем.
Внутренние блокировки накладываются, чтобы защитить внутренние структуры Oracle, например файлы.
Внимание: Не существует одного простого способа работы с блокировками. Это сделало бы работу разработчика чересчур простой. Вы наверняка не захотите, например, устанавливать блокировку при изменении строки в случае, если пользователь выдает команду SELECT:FOR UPDATE, а затем отправляется на обед или начинает длительный разговор по телефону. Если же это происходит, строка остается блокированной до тех пор, пока пользователь не возвращается к работе с приложением. И это соображение становится все более важным по мере роста размера базы и числа пользователей. Вот почему может оказаться более эффективным использовать стандартные системы блокировки, встроенные в Oracle и не применять SELECT:FOR UPDATE.
Существуют две основные стратегии применения блокировок для приложений, работающих с базами данных: пессимистическая и оптимистическая. Пессимистическая стратегия (pessimistic locking strategy) состоит в том, что вы предполагаете реализацию самого худшего сценария: другие пользователи непременно попытаются изменить строку, с которой вы работаете. Для реализации этой стратегии в Oracle следует установить блокировку на строке данных, которые вы хотите обновить. Это гарантирует высокую степень целостности данных. Однако, пессимистический подход приводит к проблемам доступа к данным.
Оптимистическая стратегия (optimistic locking strategy) предполагает иной вариант: никто не попытается изменить строку перед тем, как вы сами сделаете это. Для реализации этой стратегии в Oracle необходимо работать с данными без наложения блокировок.
Oracle не содержит такого элемента, как ROWID в SQLBase (Напомню, что в SQLBase ROWID изменяется всякий раз, когда происходит изменение данных в строке таблицы. Поэтому ROWID является основой методик детектирования изменения содержимого обрабатываемой строки данных без наложения на нее блокировок). ROWID в Oracle описывает только физическое положение строки и не изменяется до тех пор, пока строка не будет физически перемещена в другое положение. В конечном итоге, одни приложения хорошо подходят для оптимистической стратегии, в то время как другие могут потребовать реализации пессимистической стратегии. Только разработчик должен решить, какой тип стратегии блокировок применить в конкретном приложении SQLWindows.
Внимание: СУБД Oracle создана с ориентацией на пессимистическую стратегию, а SQLBase - на оптимистическую.
В многопользовательской среде сразу несколько пользователей могут работать с одними и теми же данными. Поэтому проблема доступа к данным является очень актуальной. Несмотря на то, что Oracle использует блокировки в качестве механизма управления многопользовательским доступом, одних блокировок иногда бывает недостаточно. Поскольку более одного пользователя могут выбрать ту же строку данных, они также могут обновить эту строку. При этом возникает проблема результирующего значения и достоверности при множественных обновлениях одних и тех же строк. Oracle, с учетом имеющихся возможностей блокировок, не поддерживает разрешение такой ситуации автоматически.
Приложение, с другой стороны, может само обрабатывать обновление строк. При этом можно использовать несколько возможностей для управления целостностью и доступом к данным:
Если не требуется обеспечивать межплатформенность приложения, использовать генератор последовательностей в Oracle и последовательные колонки в таблицах.
В зависимости от того, какую возможность вы используете, это означает тот или иной тип компромисса. Вы должны понимать природу этого компромисса и его ограничения. Исходя из этого, вы сможете определить, какой из вышеперечисленных методов наилучшим образом подходит для вашего приложения.
Реализация ограничений ссылочной целостности (referential integrity constraints) с помощью встроенных возможностей Oracle обычно более предпочтительна, чем непосредственно в приложении. Однако, Oracle всегда проверяет существование родительской строки перед вводом каждой новой дочерней строки. В результате, массированный ввод строк в дочернюю таблицу может привести к большому количеству ненужных проверок и отразиться на производительности. Ограничения ссылочной целостности в Oracle должны быть описаны при создании таблиц.
Внимание: При "включенной" ссылочной целостности весьма важным становится порядок выполнения команд SQL.
Операция UPDATE, основанная на полном содержимом строки данных Для выполнения этого метода обновления данных вы можете выполнять обновление, основываясь на значении первичного ключа и старых значениях всех колонок, которые обновляются. В этом случае более одного пользователя могут обновлять одну и ту же строку без проблем до тех пор, пока они не изменяют одни и те же колонки. В другом варианте этого метода вы можете производить обновление всей строки, основываясь на старом значении всей строки, вне зависимости от того, какие колонки были изменены. Например, вы выбрали колонки company_code, company_name, company_addr1 и company_addr2 из таблицы company.
Call SqlPrepareAndExecute
(
hSql, "select company_code, company_name, company_addr1, company_addr2, company_name, company_addr1, company_addr2
from company
into :dfCompanyCode,
:dfCompanyName,
:dfCompanyAddr1,
:dfCompanyAddr2,
:dfOriginalName,
:dfOriginalAddr1,
:dfOriginalAddr2"
)
Допустим, вы изменили колонки company_name, и company_addr1. Тогда ваша команда UPDATE должна включать оригинальные значения этих колонок в предложении WHERE.
Call SqlPrepareAndExecute
(
hSql, "update company
set company_name =
:dfCompanyName,
company_addr1 =
:dfCompanyAddr1
where company_code =
:dfCompanyCode
and company_name =
:dfOriginalName
and company_addr1 =
:dfOriginalAddr1"
)
При таком методе вы должны контролировать, какие колонки изменяются пользователем и только их включать в команду UPDATE с тем, чтобы не переписать изменения других пользователей. При этом, естественно, нельзя использовать большинство из имеющихся в Oracle возможностей блокировок.
Использование режимов ROW SHARE и ROW EXCLUSIVE приведет к наложению блокировок на данные. Эти блокировки остаются на строках длительное время и препятствуют обновлению этих блокированных строк другими пользователями. Поэтому следует применять частые COMMIT"ы для своевременного сброса установленных блокировок.
Хотя Oracle, подобно SQLBase, имеет в каждой таблице колонку ROWID, ROWID в Oracle не несет в себе никакой временной информации. Она представляет собой физический указатель на положение строки в базе данных. Вы можете использовать ее для ускорения выборки данных, но не для проверки многопользовательского доступа.
Для использования метода "версии строки" (rowversion) создайте во всех таблицах специальную колонку. В приведенных ниже примерах используется колонка ROWVERSION. Типом данных этой колонки будет NUMBER. Использование типа DATETIME не рекомендуется, потому что точность данного типа составляет только одну минуту. Для изменения значения ROWVERSION при каждом обновлении строки используйте генератор последовательностей.
Включите колонку ROWVERSION в предложение WHERE команды UPDATE и проверяйте каждый раз число измененных строк. Или перед командой COMMIT удостоверьтесь в том, что текущее значение колонки ROWVERSION соответствует тому, которое вы извлекли из базы в начале работы. Не забудьте проводить частые COMMIT"ы для сброса блокировок. Ниже приведен пример таблицы с колонкой ROWVERSION:
CREATE TABLE COMPANY (ID INTEGER NOT NULL,
NAME VARCHAR(30) NOT NULL,
ADDR1 VARCHAR (30),
ADDR2 VARCHAR (30),
CITY VARCHAR (30),
STATE VARCHAR (2),
ZIP VARCHAR (10),
COUNTRY VARCHAR (30),
PHONE VARCHAR (17),
FAX VARCHAR (17),
ROWVERSION INTEGER)
Затем, следует создать генератор последовательности для колонки ROWVERSION:
CREATE SEQUENCE COMPANY_ROWVERSION
NOMAXVALUE
MINVALUE 0
ORDER
Для ввода новой строки в таблицу COMPANY из приложения проделайте следующую операцию:
Call SqlPrepareAndExecute
(
hSql, "insert into company (id, name, addr1, addr2, city, state, zip, country, phone, fax, rowversion)
values (:dfId, :dfName, :dfAddr1, :dfAddr2, :dfCity, :dfState, :dfZip, :dfCountry, :dfPhone, :dfFax, COMPANY_ROWVERSION.NEXTVAL)"
)
Для обновления существующей строки в таблице COMPANY и проверке количества модифицированных строк следует выполнить:
Call SqlPrepareAndExecute
(
hSql, "update company set
rowversion = company_rowversion.nextval,
name = :dfName,
addr1 = :dfAddr1,
addr2 = :dfAddr2,
city = :dfCity,
state = :dfState,
zip = :dfZip,
country = :dfCountry,
phone = :dfPhone,
fax = :dfFax
where id =:dfId
and rowversion = :dfRowVersion"
)
Call SqlGetModofiedRows
( hSql, nResult )
If nResult != 1
Set nSqlError = nINVALIDROWID
Call SalMessageBox("Строка
изменена или удалена другим
пользователем",
"Update", MB_Ok )
Return FALSE
Проверку строки можно осуществлять и перед выполнением операции UPDATE:
If SqlPrepareAndExecute( hSql, "select rowversion
from company
into :nRowVersion
where id = :dfId" )
Call SqlFetchNext (hSql,
nFetch )
If nRowVersion = :dfRowVersion
Call SqlPrepareAndExecute(hSql,
"update company set
rowversion = company_
rowversion.nextval,
name = :dfName,
addr1 = :dfAddr1,
addr2 = :dfAddr2,
city = :dfCity,
state = :dfState,
zip = :dfZip,
country = :dfCountry,
phone = :dfPhone,
fax = :dfFax
where id =:dfId
and rowversion = :dfRowVersion" )
Else
Set nSqlError = nINVALIDROWID
Call SalMessageBox( "Строка
изменена или удалена другим
пользователем",
"Update", MB_Ok )
Return FALSE
Внимание: Когда выполняется команда UPDATE, Oracle копирует начальный набор изменяемых данных в так называемый "rollback segment". Другие пользователи по-прежнему могут извлекать оригинальную, неизмененную строку, поскольку Oracle не блокирует команды SELECT. Эти пользователи, однако, не увидят новых значений до тех пор пока команда UPDATE не будет подтверждена командой COMMIT. Вот почему важным моментом является применение частых COMMIT"ов.
Существует целый ряд особенностей, которые отличают Oracle от SQLBase, и которые следует учитывать при создании приложений SQLWindows. Вам следует знать об этих различиях, чтобы извлекать данные в соответствующие переменные и правильно конструировать SQL запросы. В SQLWindows существуют много функций, предназначенных специально для работы с Oracle. Эти функции будут рассмотрены ниже наряду с вопросами оптимизации приложений.
Аналогично SQLBase, в Oracle не нужно указывать начало транзакции с помощью команд типа BEGIN WORK или BEGIN TRANSACTION.
Вы обязаны использовать команду COMMIT для подтверждения успешного окончания транзакции. Oracle не поддерживает такое свойство как AUTOCOMMIT. Поэтому, если вы устанавливаете опцию autocommit в файле SQL.INI или с помощью функции SqlSetParameter() в приложении SQLWindows, SQLRouter/Oracle выпускает COMMIT после каждого выполнения запроса SQL. Выключите опцию autocommit если вам требуется возможность реализации длинных, многозапросных транзакций или вы хотите иметь возможность откатить результат сразу нескольких команд SQL.
Мы уже использовали последовательности для создания и управления версией обрабатываемой строки. Последовательности также могут применяться для генерации первичных ключей в таблицах.
Подобно SQLBase Oracle имеет набор встроенных в СУБД функций, которые могут быть использованы в SQL запросах.
Функция SQLBase @UPPER() преобразует значение колонки, переданной в функцию в качестве параметра, в верхний регистр. Например:
Call SqlPrepareAndExecute( hSql, "SELECT @UPPER(NAME)
FROM COMPANY
INTO :dfName
WHERE ID - 1" )
В Oracle то же самое делает функция upper():
Call SqlPrepareAndExecute( hSql, "SELECT upper(NAME)
FROM COMPANY
INTO :dfName
WHERE ID - 1" )
SQLRouter/Oracle поддерживает использование хранимых процедур Oracle с помощью функции SqlPLSQLCommand() из приложений, написанных на SQLWindows. Существуют две основных причины в пользу применения хранимых процедур:
1. Оптимизация. Запросы SQL часто выполняются гораздо быстрее из хранимой процедуры, чем из отдельной команды, выпущенной из приложения. Использование хранимых процедур как правило приводит к уменьшению сетевого трафика и улучшению времени отклика.
2. Централизация кода. Когда вы изменяете текст хранимой процедуры, только эта процедура должна быть перекомпилирована в базе данных. При этом конечное приложение остается без изменений и его не требуется распределять по всем рабочим станциях конечных пользователей приложения. Используйте функцию SqlPLSQLCommand для выполнения существующих хранимых процедур на PL/SQL из приложения SQLWindows. IN переменные PL/SQL могут быть использованы точно так же, как все остальные параметры привызове функций SAL. Параметры OUT и IN/OUT должны соответствовать существующим возвращаемым параметрам SQLWindows. Вы можете использовать массивы в качестве параметров IN, OUT и IN/OUT. Использование массивов дает вам возможность извлекать много строк данных за одну операцию FETCH. Массивы также можно использовать в качестве bind переменных.
Вы можете создавать собственные хранимые процедуры из приложений SQLWindows с помощью функций SqlPrepare() и SQLExecute(). Ниже приведен пример использования массивов в качестве параметров хранимой процедуры.
String: sCompanyCode[3]
String: sCompanyName[3]
Set sCompanyCode[0] = "ABC"
Set sCompanyCode[1] = "INT"
Set sCompanyCode[2] = GUP"
Set sCompanyName[0] = "ABC Company"
Set sCompanyName[1] = "Interface Ltd."
Set sCompanyName[2] = "Gupta Corporation"
Call SqlPLSQLCommand( hSql, "COMPANY.INSERT_COMPANY( sCompanyCode, sCompanyName )" )
Триггеры представляют собой особый вид хранимых процедур. Обычно они используются для поддержки ссылочной целостности в базе данных. Триггеры также могут применяться для реализации бизнес-правил. Они выполняются автоматически каждый раз при выполнении команды INSERT, UPDATE или DELETE. Триггер, определенный на одной таблице, может запустить триггер в другой таблице и так далее.
Хотя вы можете поддерживать ссылочную целостность данных только с помощью триггеров, комбинация из триггеров и кода на SQLWindows является более предпочтительной. При этом вы имеете возможность осуществлять проверки ссылочной целостности ДО выполнения триггера, что позволяет во многих случаях существенно сократить время блокировки данных.
Используйте таблицу DUAL в запросах SELECT, не использующих предложение FROM. Ниже приведен пример команды SELECT, используемой для получения значения системной даты:
If SqlPrepareAndExecute( hSql, SELECT sysdate FROM DUAL" ) Call SqlFetchNext( hSql, nFetch )
Ниже приведены функции SQLWindows, которые могут быть использованы приложениями, работающими с Oracle, для доступа к данным и для установки параметров, таких как LongBuffer, MapError, FetchRow, во время выполнения приложения. Функция SqlPLSQLCommand введена специально для работы с СУБД Oracle.
SqlSetLongBindDatatype()
SqlPLSQLCommand()
SqlExists()
SqlImmediate()
SqlClearImmediate()
SqlPrepare()
SqlExecute()
SqlPrepareAndExecute()
SqlGetResultSetCount()
SqlSetInMessage()
SqlSetOutMessage()
SqlSetLockTimeout()
SqlSetParameter()
SqlSetParameterAll()
Существуют несколько параметров Oracle, которые могут быть установлены во время выполнения приложения SQLWindows с помощью функций
SqlSetParameter()/SqlSetParameterAll():
DBP_LONGBUFFER
DBP_MAPERROR
DBP_FETCHROW
При всем обилии возможностей конфигурирования клиентского приложения SQLWindows для работы с Oracle, необходимо знать, когда и какие возможности следует применять.
Ниже обсуждаются некоторые наиболее важные из этих возможностей.
Front-End Result Sets
SQLRouter/Oracle использует множества результатов на клиентской машине (frontend result sets) для эмуляции прокручиваемых курсоров (scrollable cursors). Такая эмуляция может снизить производительность. Вы можете отключить режим front-end result set с помощью вызова функции SqlSetResultSet( hSql, FALSE ). Отключайте режим front-end result set только в том случае, если вы не используете в приложении функции SqlFetchPrev() или SqlFetchRow() для данного курсора. Например, если курсор используется для заполнения таблицы в режиме TBL_FillNormal, вы не можете отключить front-end result set.
Ключевое слово fetchrow в файле SQL.INI управляет использованием массивов с Oracle. Оно определяет максимальное число строк, извлекаемых из базы данных за одну операцию FETCH.
Вы должны стараться каждый раз определить оптимальное значение параметра fetchrow, поскольку чем больше извлекается строк, тем больше памяти требуется для размещения данных на клиентской машине. Это может привести к интенсивному использованию файла подкачки Windows и, соответственно, к снижению производительности.
Ключевое слово longbuffer в файле SQL.INI (секция [oragtwy]) определяет размер внутреннего буфера, используемого для чтения и записи данных типа LONG. Уменьшение размера буфера может освободить дефицитную память Windows и уменьшит сетевой трафик. Вы также можете изменить значение ключевого слова longbuffer с помощью функций SqlSet Parameter ()/SqlSetParameterAll().
Множественные обновления в базе данных могут создать проблемы с производительностью. Напрмер, вам может понадобиться провести обновление большого числа строк в таблице COMPANY из экранной таблицы:
Call SalTblQueryScroll( tbl1, nRow, nMinRange, nMaxRange)
Set nRow = nMinRange
While nRow <= nMaxRange
Set nRowVersion = tbl1.colRowVwersion
Call SqlPrepareAndExecute( hSql, "update company set
rowversion = company_rowversion.nextval,
name = :tbl1.colName,
addr1 = :tbl1.colAddr1,
addr2 = :tbl1.colAddr2,
city = :tbl1.colCity,
state = : tbl1.colState,
zip = : tbl1.colZip,
country = : tbl1.colCountry,
phone = : tbl1.colPhone,
fax = : tbl1.colFax
where id =:tbl1.colId
and rowversion = :nRowVersion" )
Set nRow = nRow + 1
Вынос функции SqlPrepare() за пределы цикла While может существенно повысить производительность вашего приложения:
Call SalTblQueryScroll( tbl1, nRow, nMinRange, nMaxRange)
Set nRow = nMinRange
Call SqlPrepare( hSql, "update company set
rowversion = company_rowversion.nextval,
name = :tbl1.colName,
addr1 = :tbl1.colAddr1,
addr2 = :tbl1.colAddr2,
city = :tbl1.colCity,
state = : tbl1.colState,
zip = : tbl1.colZip,
country = : tbl1.colCountry,
phone = : tbl1.colPhone,
fax = : tbl1.colFax
where id =:tbl1.colId
and rowversion = :nRowVersion" )
While nRow <= nMaxRange
Set nRowVersion = tbl1.colRowVwersion
Call SqlExecute( hSql )
Set nRow = nRow + 1
СУБД Oracle является одной из наиболее мощных и производительных в отрасли. Но для того, чтобы полностью использовать все предоставляемые Oracle возможности, вы должны научиться создавать такие же эффективные клиентские приложения. Среда разработки приложений SQLWindows позволяет вам это сделать.
|
|