back
Autor : Sancho Fock
In client server databases, many users access the same resources, a situation which inevitably leads to scale blocks. This is not a problem if the individual lockouts (and/or their transactions) are only of short-time duration. According to this definition, "short-time" refers to transactions which do not last longer than three seconds. Incidents of this kind of short-time scale blocks can be ignored except in cases where they occur so frequently that they lead to snowball effects.
The situation is different when mid-time transactions (average duration ranges between approximately 4 seconds and 10 minutes) or even long-time transactions (average duration extends beyond 10 minutes) occur in the system. These kinds of scale blocks indicate the presence of an error in the application. (More information concerning resolution strategies for the different types of scale blocks can be found in the article Anti-Blocker Strategies by the same author).
The long-time transactions can be readily recognized using manual tools - as described, for example, in the Microsoft Knowledge Base (Art. Q271509 and Q224453 ). The mid-time transactions, on the other hand, are different: they continue for a long enough time to bring the operation of an application to a stop, but are usually too brief for one to be able to identify them manually. This is where the SQL Guard 2000 comes into play: as soon as it has identified a scale block, it enters the information in a protocol. In addition, it ensures that the operation of the other users can be maintained, despite the long-time scale block, by terminating the blocking process when there is a scale block which has continued for too long (interval can be set).
An example will be presented below to illustrate the mode of operation of the SQL Guard 2000:
The SQL Guard 2000 can be started once the database link of the SQL Guard has been set up in the SQLGuard.Udl file. The intervals can then be set to determine when the tests are to be carried out.
The following intervals have been configured for this example:
Check interval = 10 seconds
Kill interval = 3 tests
Based on these settings, a process can block other processes for only a maximum of 39 seconds before it itself is terminated.
Once the intervals are set, the testing of blocks is activated and monitoring can be started. The SQL Guard announces the successful link with the database server and is ready.
top of page
A scale block has been generated in order to offer a demonstration of the precise method of operation of the SQL Guard 2000. For this, a window opens in the Query Analyser (QA) and the following transaction is executed:
begin transaction
select * from customers
update customers
set City = 'GB-London'
where City = 'London'
while (1=1) begin
select @@version
end
rollback transaction
This gives us a typical blocker process. The actual
scale block does not however occur until a user interrogates
the locked resource. A second QA window is now opened for that
reason and the following interrogation is executed:select * from customersA scale block has thus come into being: the process in the first QA window blocks the process in the second QA window.
The SQL Guard notices this at once:
top of page
What information is now offered by the SQL Guard 2000?
For this, one must look somewhat more closely at the log: first comes the announcement of the scale block which has been detected, after which follows all of the important information concerning the scale block:
17.02.2003 22:53:21 *** SQL Guard LOCKCHECK: Blocking situation ascertained :
| Process : 8 | Process ID of the blocker |
| Blocked Process : 9 | Process ID of the blocked process |
| Station : THS-MOB | Name of the workstation of the blocker process |
| User : | User name of the blocker (only important if the process has announced itself with integrated security) |
| Domain : | Domain of the blocker (only important if the process has announced itself with integrated security) |
| Login : sa | SQL Server login name of the blocker |
| Database : Northwind | Name of the database at which the scale block has taken place. |
| Application : MS SQL Query Analyze | Name of the application which has caused the block |
| CPU : 0 | CPU Status of the blocker process |
| IO : 54 | IO of the blocker |
As a rule, this information is sufficient with a two-tier application for finding that part of the program which contains the error. As an additional security measure for error identification, the users who had carried out the blocking process (and whose database process has been terminated) receive a message.
This information is however not sufficient in cases of multi-tier applications for which the end user cannot be identified from the workstation of the SQL-Server client process. More information is required for such cases.
(Plans have been made to provide later versions with interfaces for J2EE Container, MTS and CORBA objects in order that they may receive the necessary information from the application server.)
Below is a list of the resources which the blocker process has locked, followed by specifications concerning the input buffer of the blocker:
Blocked Resouce(es)
ObjectID --> ObjectName
213575799 --> Customers
BLOCKING Statement : Typ: Language Event - Parameter: 0 -
SQL: begin transaction
select * from customers
update customers
set City = 'GB-London'
where City = 'London'
while (1=1) begin
select @@version
end
rollback transaction
BLOCKED Statement : Typ: Language Event - Parameter: 0 -
SQL: select * from customers
top of page It is clear from the input buffer which interrogation of the SQL Server the blocking process had made prior to its termination. Based on this specification, the interrogation which has thus been made known can now be sought in the application, which means in turn that the transaction can be located which caused the problem in the first place.
The search for the transaction is however made more difficult by the fact that the statement in the input buffer of the blocker need not necessarily be the statement which caused the block (it is however in any event a statement contained in the same transaction).
For this reason, the SQL Guard 2000 also displays a list of the blocked resources so that with the aid of this information it will be easier to be able to identify the actual transaction causing the trouble.
Example: a transaction which locked the table "Orders", among other things, probably has to do with the application's product ordering module.
Should it happen however that this information continues to be insufficient for locating the transaction being sought, then a glance at the output buffer of the blocker will provide further help; it contains an extract from the data which the SQL Server had most recently sent to the blocking process.
Outputbuffer : v.M.i.c.r.o..o.f.t. .S.Q.L..S.e.r.v.e.r. ..7...0.0. .-. ....0.0...6.2.3..(.I.n.t.e.l. ..8.6.). .....N..v. .2.7. .1.9..8. .2.2.:.2.0..0.7. .....C.o..y.r.i.g.h.t. ..c.). .1.9.8.8..1.9.9.8. .M .i..r.o.s.o.f.t. ..o.r.p.o.r.a.t.. o.n.....D.e.v..l.o.p.e.r. .E..i.t.i.o.n. .o.. .W.i.n.d.o.w. . .N.T. .5...1..(.B.u.i.l.d. ..6.0.0.:. .S.e..v.i.c.e. .P.a..k. .1.).......................... .v...v.M.i..r.o.s.o .f.t. ..Q.L. .S.e.r.v..r. . .7...0.0..-. .7...0.0....2.3. .(.I.n.t.The concluding protocol record of the SQL Guard 2000 contains information regarding how long the blocker blocked and whether or not it was terminated:
*** SQL Guard LOCKCHECK: The blocking Process 8 is still blocking! Minimum age: 10 seconds *** SQL Guard LOCKCHECK: The blocking Process 8 is still blocking! Minimum age: 20 seconds *** SQL Guard LOCKCHECK: The blocking Process 8 was terminated by SQL GuardIn the event of a blockingsituation, the SQL Guard 2000 provides the information which is necessary for locating the error. The protocol record offers the advantage of making it possible at any later time to retrace the events which occurred in the database, without being forced to have been in the immediate vicinity at the time of the block. This example has shown that the SQL Guard 2000 is also in a position of being able to ensure secure operation, even in the event of grave errors. On the one hand, this offers the developers the opportunity of recognizing problems, while at the same time giving them (as a rule) sufficient time for careful analysis of the problem on the other.
It is not always a simple matter to find the reason for a scale block. In order to be able to develop reasonable strategies, it is imperative that one be aware of which kinds of errors cause scale blocks. The SQL Guard 2000 offers you the possibility of quickly tracing the trouble back to its roots.
A hands-on opportunity is available for you to convince yourself of the advantages of the SQL Guard:
a demo version of SQL Guard 2000 can be downloaded free-of-charge here
back