SQL ServerとOracleのデータロック防止の比較-php手册-php.cn

オラクル|サーバー|比較|データ

Liao Zheng

2002-5-30 14:23:50

---------------------------------- --------------------------------------------------

データベース並列アクセス、つまり 2 人以上のユーザーが同じデータに同時にアクセスすることは、適切な応答を設計および実装する方法においてデータベースエンジンが直面する最大の問題でもあります。適切に設計された高性能のデータベースエンジンは、数千のユーザーに同時に簡単にサービスを提供できます。「信頼性が不十分」なデータベースシステムは、より多くのユーザーが同時にシステムにアクセスするため、パフォーマンスが大幅に低下します。最悪の場合、システムがクラッシュする可能性もあります。

もちろん、並列アクセスはデータベースソリューションにとって最も重要な問題であり、並列アクセスの問題を解決するために、さまざまなデータベースシステムがさまざまなソリューションを提案しています。 SQL Server と Oracle という 2 つの主要な DBMS も、異なる並列処理方式を採用しています。それらの大きな違いは何ですか?

並列アクセスに関する問題
並列アクセスで問題が発生する状況がいくつかあります。最も単純なケースでは、複数のユーザーが同時に同じデータをクエリする可能性があります。この場合、データベースの運用目標は単純です。ユーザーに可能な限り高速なデータアクセスを提供することです。これは一般的なデータベースでは問題になりません。SQL Server と Oracle はどちらもマルチスレッドメカニズムを使用しており、もちろん一度に複数のリクエストを処理できます。

しかし、ユーザーがデータを変更すると、並列アクセスの問題は複雑になります。明らかに、データベースでは通常、一意のユーザーのみが一度に特定のデータを変更できます。ユーザーがデータの変更を開始すると、SQL Server と Oracle の両方がデータをすぐにロックし、データを変更した最初のユーザーが操作を完了してトランザクションを送信 (トランザクションのコミット) するまで、他のユーザーがデータを更新できないようにします。しかし、ユーザーがデータの一部を変更していて、別のユーザーがそのデータに関する情報をクエリしたい場合はどうなるでしょうか?この場合、データベース管理システムはどのように動作すべきでしょうか? Oracle と SQL Server は、この問題に対して異なる解決策を持っています。

SQL Server メソッド
次に、誰かが SQL Server に保存されているデータを変更し始めたため、このデータはデータベースによって即座にロックされたと仮定します。データロック操作は、クエリ操作を含め、データにアクセスする他の接続をブロックします。したがって、このロックされたデータは、トランザクションが送信またはロールバックされた後にのみ他のアクセス操作を受け入れることができます。

以下は、SQL Server に付属の pubs サンプルデータベースを使用した簡単なデモンストレーションです。クエリアナライザー内で 2 つのウィンドウを開きます。最初のウィンドウで次の SQL 操作ステートメントを実行して、Pubs データベース内の特定の書籍の価格を更新します。

use pubs
go
begin tran
タイトルを更新
set 価格 = 価格 * 1.05
where

title_id = 'BU2075 '

コード内で commit ステートメントが実行されていないため、実際にはデータ変更操作はまだ完了していません。次に、別のウィンドウで次のステートメントを実行して、タイトルデータテーブルをクエリします:
title_id,title,price
from Titles
order by title_id
結果は得られません。ウィンドウの下部にある小さな地球のアイコンが回転します。前回の操作では 1 行しか更新していませんでしたが、たまたま select 文の実行オブジェクトにデータを変更中の行が含まれていました。したがって、最初のウィンドウに戻ってトランザクションを送信するかロールバックしない限り、上記の操作ではデータは返されません。

SQL Server のデータロックスキームにより、システムのパフォーマンスと効率が低下する可能性があります。データがロックされている時間が長いほど、またはロックされているデータの量が多いほど、他のデータアクセスユーザーがクエリの実行を待たなければならない可能性が高くなります。したがって、プログラマの観点から見ると、SQL Server をプログラミングするときは、トランザクションコードが小さくて高速になるように設計する必要があります。

SQL Server の最新バージョンでは、Microsoft は SQL Server に特定の変更を加え、一度にロックされるデータの量を大幅に削減しました。これはデータベース設計における重要な改善です。バージョン 6.5 以前では、ロックされるデータの最小量は 1 ページでした。 1 行のデータのみを変更し、そのデータ行が 10 行のデータを含むページ上にある場合でも、10 行のデータのページ全体がロックされます。明らかに、このような大量のデータロックにより、他のデータアクセス接続がデータ修正が完了するまで待たなければならない可能性が高くなります。 SQL Server 7 では、Microsoft は行ロックテクノロジを導入し、現在の SQL Server は実際に変更されているデータ行のみをロックします。

SQL Server のソリューションはシンプルに聞こえますが、実際には、十分なシステムパフォーマンスを提供するために舞台裏で多くの対策が講じられています。たとえば、複数のデータ行を同時に変更すると、SQL Server はデータロックの範囲をページレベルに拡大したり、データテーブル全体をロックしたりするため、レコードごとに個別のデータロックを追跡および維持する必要がなくなります。

Oracle メソッド
Oracle データベースが同様の操作をどのように実装するかを見てみましょう。まず、SQLPlus インスタンスを開いて次のクエリを実行しました (この例は Oracle 9i の例にあります)。このインスタンスはクエリインスタンスと呼ばれます:
first_name、last_name、salary を選択します
from hr.employees
where
Department_id = 20;
このコードは、以下に示すように 2 行のデータを返します。

次に、別の SQLPlus インスタンスを開き、次のコマンドを実行します。
SQL> update hr.employees
2 set給与 = 給与 * 1.05
3 where
4 部門ID = 20
5 /
コードが実行されると、2 行のデータが更新されたことを示すメッセージが返されます。
上記のコードには、SQL Server の例のような「begin tran」の入力が含まれていないことに注意してください。 Oracle の SQLPlus は、トランザクションを暗黙的に有効にします (SQL Server の動作を模倣して、「自動コミットをオン」に設定してトランザクションを自動的にコミットすることもできます)。次に、SQLPlus 更新インスタンスのクエリインスタンスと同じ選択ステートメントを実行します。

結果は明確に示しています: Michael と Pat の給与は両方とも増加しましたが、現時点ではデータ変更トランザクションを送信していません。
最初の SQLPlus クエリインスタンスに移動し、クエリを再実行します。結果は次のとおりです。

Oracle は、データ更新インスタンスの操作が送信されるまでユーザーを待つ必要はありません。クエリ情報を直接返します。 Michael と Pat の例ですが、実際には、開始する前にデータの更新が返されます。

このとき、SQL Server に詳しい人は、クエリに (NOLOCK) を設定しても同じ効果が得られるのではないかと言うかもしれません。ただし、SQL Server の場合、データイメージより先にデータを取得することはできません。 (NOLOCK) を指定すると、実際にはコミットされていないデータが取得されるだけです。オラクルのアプローチは、すべての情報がトランザクション固有であり、保存されたデータのスナップショットに基づいているため、データの一貫したビューを提供します。

SQLPlus の更新インスタンスで更新トランザクションを送信すると、クエリインスタンスで給与データの変更を確認できます。クエリインスタンスで前のクエリステートメントを再実行すると、Oracle は新しい給与値を返します。

ストレージデータのスナップショット
長い間話をした後、Oracle は、ユーザーに以前のバージョンのデータを表示しながら、他のユーザーがデータを変更できるようにするにはどうすればよいでしょうか?実際、ユーザーがデータを変更するトランザクションを開始する限り、以前のデータイメージは特別な記憶領域に書き込まれます。この「変更前イメージ」は、データをクエリするすべてのユーザーにデータベースの一貫したビューを提供するために使用されます。このようにして、他のユーザーがデータを変更しているときに、上記のテストでは変更されていない給与データを確認できます。

この特別な保管場所はどこですか?この質問に対する答えは、使用している Oracle のバージョンによって異なります。 Oracle 8i 以前のバージョンでは、この目的のために特別なロールバックセグメントが作成されました。ただし、このアプローチでは、データベース管理者 (DBA) にデータセグメントの管理と調整の負担がかかります。たとえば、DBA は、この目的に必要なデータセグメントの数とサイズを決定する必要があります。ロールバックセグメントが正しく構成されていない場合、ロールバックセグメントで使用できるようになるために、トランザクションは必要なデータスペースをキューに入れる必要がある場合があります。

Oracle 9i は異なります。これは Oracle の最新バージョンであり、Oracle は、上記の管理の複雑さを効果的に排除する、いわゆる UNDO テーブルスペースという新機能を実装しました。ロールバックセグメントは引き続き使用できますが、DBA は UNDO 表スペースを作成し、「ビフォアイメージ」自体の複雑なスペース割り当てを Oracle に管理させることを選択できるようになりました。
Oracle のアプローチはプログラマーにとって非常に重要です。ロールバックスペースは無限ではないため、更新されたトランザクションのデータスナップショットによって前のトランザクションのイメージが置き換えられます。したがって、必要に応じて、ロールバックセグメントは他のトランザクションのイメージによって上書きされます。クエリ操作を長時間実行すると、「スナップショットが古すぎます」エラーが発生する場合があります。

以下のようなケースが考えられます。午前 11 時 59 分に事務員が John Doe の口座のトランザクションの更新を開始するとします。トランザクションは午後 12 時 1 分に送信されました。同時に、午後 12 時に、財務マネージャーがすべての顧客の会計明細とその月の合計料金の照会を開始しました。顧客が多いため、このクエリ操作には時間がかかりますが、操作にどれだけ時間がかかっても、取得される結果は午後 12 時の時点でデータベースに存在するデータです。顧客名に対してクエリを実行すると、John Doe のアカウント前のイメージを含むロールバックスペースが上書きされると、クエリはエラーメッセージを返します。
Oracle のソリューションは確かにより合理的であり、抽象的な意味で SQL Server よりも優れたデータ一貫性を提供します。 Oracle クエリを実行する場合、長いクエリ操作によって重要なトランザクションがロックされることを心配する必要はありません。ただし、両方のデータベースが同時に多数のユーザーをサポートする場合、Oracle が特定の条件下でデータの一貫性を本当に実現できるかどうかを証明するのは困難です。