The Lock Print Utility - Firebird

The program that extracts the lock table statistics is an executable named fb_lock_print, which you will find in the / bin directory of your Firebird installation. (For v.1.0.x, look for iblockpr.exe on Windows, or gds_lock_pr on POSIX.) Two syntaxes are available: one for static reports, the other for capturing a sampling interactively at specified intervals.

This is the syntax for Firebird 1.5 and higher:

v.1.0.x, POSIX:

v.1.0.x, Windows:

The fb_lock_print program accepts a number of switches, described in Table. When no switches are provided, fb_lock_print prints summary information describing the lock header and the owners communicating with the lock manager.

Switches for Lock Print Reports

Static Reports

Static reports print a snapshot of the lock table. Any switches are valid except –i, and you can “stack” multiple switches into one. For example, to print the “waiting on” graph plus the history block:

Interactive Reports

The second form collects a specified number of samples at fixed intervals and produces an interactive report monitoring current lock table activity. The syntax is

t specifies the time in seconds between samplings. n specifies the number of samples to be taken. If you do not supply values for n and t, the default is n = 1.

Sampling occurs n times at intervals of t seconds. One line is printed for each sample. The average of the sample values is printed at the end of each column.

The following statement prints “acquire” statistics (access to lock table: acquire/s, acqwait/s, %acqwait, acqrtry/s, and rtrysuc/s) every 3 seconds until 10 samples have been taken:

At the end of the chapter is a snapshot of an interactive report with explanations of the meanings of the columns.

Reporting to a File

The results are usually quite long for viewing on the console. You can supply a pipe to an output file, for example in a directory named /data/server_reports/ (your choice!), like this: fb_lock_print -a > /data /server _reports/ lock.txt. If you find a lock print running for more than a minute or two, or find that it is filling your disk, kill it with Ctrl+C or your platform equivalent.

The blocks are listed in the order of the internal lists. New blocks are put at the head of the list, so a newly minted lock table will be shown with blocks in reverse numeric order. As lock, request, and owner blocks are released and reused, the order becomes thoroughly scrambled. A text editor is very useful for chasing through the relationships.

A Simple Lock Print

We’ll take a look at an example from a very simple static lock print with no switches.

• The lock header is always first.
• Next come the owner blocks—owner block followed by all the requests for that owner. Each owner in the chain is printed with its requests.
• After all owners and requests come the locks.
• The last items are the history records.

The Lock_Header Block

First, we’ll consider only the lock header block that describes the general shape and condition of the lock table. In Figure, numbers have been added for reference to each item of explanation in Table.

Our lock print represents a database that has just been created and is being accessed by a single copy of isql on the v.1.0.xWindows Superserver version.

The Lock Header block

Lock Header Block Entries

Owner Blocks

An owner block, depicted in Figure, describes a transaction or other user of the Lock Manager. Owners fall into one of several types, identified by one of five numbers:

• 1: Process.
• 2: Database.
• 3: Client attachment. In Classic server, client attachments are always processes.
• 4: Transaction.
• 255: Dummy process.

An owner block

The offset of the particular owner block in the lock table (here it is 11872) is also the ID used in the lock header for the “active owner,” if that owner is actively modifying the lock table. The first block printed will usually be the number printed in the Lock Header Block as the beginning of the Owner list. The value of the list pointers is actually a field in the block that contains the block’s own forward and backward pointers. Table describes the entries in an owner block.

Owner Block Entries

Table describes the states represented by the various owner flags.

Owner Flag States

Lock Blocks (Resource Blocks)

Lock blocks follow request blocks in the printout, but it makes request blocks easy if you understand lock blocks first. A lock block represents a resource that has been locked.

Lock Types: “Series”

Resource locks come in different types, or series, according to the type of resource that owners request to lock. Table identifies and describes the various resource lock types and summarizes their purposes.

Resource Types (Series)

Syntax Variation for Lock Block Reports

To print the resource lock blocks for a specific series, you need to include the series number as an argument:

Series 1: Database

The lock block in Figure represents the database itself. It is being held exclusively by one owner—itself. In Classic server, you would see several owners for the database.

A lock block, Series 1 (database)

Table explains what the report entries indicate.

Lock (Resource) Block Entries

Series 2: Relation

Figure shows a block for a relation resource (table, view).

A Series 2 (Relation resource) block

In this case, both owners are reading the relation. The key field is the RDB$RELATION_ID value for the relation. Note that both requests report their state as 2(2), indicating that they requested and received a shared read lock on the table.

Series 3: BDB Descriptor (Database Page)

Figure shows a block for a database page (BDB Descriptor) lock.

A Series 3 (BDB Descriptor) block

A BDB lock is a lock on a database page. These locks are held when two or more owners have attached a database on Classic server. They are taken when the process wants to read or write a page, and they are released when the process runs out of buffers in cache and needs to free up space or when another owner needs the page. In this example, both owners are reading (key value). On Classic server there are a lot of Series 3 type locks—one for each page buffer in the cache of each independent attachment. On Superserver, most page locks are held within the server and not expressed in the lock table.

Series 4:Transaction

Figure shows a transaction resource block.

A Series 4 (Transaction resource) block

Each action takes an exclusive lock on its own transaction ID when it starts. This block describes the state of the locks on transaction 595. One transaction is waiting for the other to finish so it can decide whether the update it wants to do is legitimate. When the owner that holds the lock departs, its locks will be released and the waiting transaction can read the transaction inventory page to determine the fate of the vanished transaction.

Series 5, 6, and 15: Existence

Figure shows an existence lock block for a relation.

Series 5, 6, and 15 Existence lock blocks

The relation existence locks (Series 5) prevent tables from being deleted while any process has a prepared request that uses that table. This lock is the source of “Object in use” errors that often occur when attempting to drop tables.

When a statement is prepared into a database “request,” the compiling process takes out a shared read lock on the existence of the relations and indexes included in the statement. Those locks are held until the request is released or the database is detached.

When a process wants to drop a relation from the database, rather than just delete its content, it must get an exclusive lock on the existence of the relation. Because no one can get an exclusive lock on a resource that is locked for shared read by another process, the shared read locks prevent the relation from being destroyed, and so prevent online metadata operations from breaking prepared requests.

This particular relation existence lock is on a relation that has an RDB$RELATION_ID of 22. The index existence locks prevent indexes from being dropped or deactivated while any process has saved a request that uses the index.

When a statement is prepared into a database “request,” the compiling process also takes out a shared read lock on the existence of the indexes included in the statement. Those locks are held until the request is released or the database is detached.

When a process wants to delete or deactivate an index, it must get an exclusive lock on the existence of the index.

Because no one can get an exclusive lock on a resource that is locked for shared read by another process, the shared read locks prevent the relation or index from being destroyed, and so prevent online metadata operations from corrupting compiled requests.

The index existence lock ID is 12000, which is the relation ID times 1000 plus the index ID. This lock records an interest in the existence of index 0 for relation 12.

The procedure existence locks are exactly like relation and index existence locks, and serve a similar purpose. The key is the procedure ID from the system table RDB$PROCEDURES.

Series 8: Shadow

Figure shows a block for locks on a shadow resource.

A Series 8 (Shadow resource) block

Every process that attaches to a database takes out a shared read lock on the state of shadowing for the database. If a process wants to add a new shadow file, it converts its lock to exclusive, which notifies all other processes that a shadow file is about to appear and they should write changes to that file. This series is used for interprocess communication in Classic server. It is also used in Superserver, although not to any great effect, since IPC is not required.

Request Blocks

Figure shows some request blocks. Table explains the meanings of the request block entries.

Some request blocks

Request Block Entries

The History Block

The Lock Manager keeps track of the I/O actions it took on behalf of any owner. The most recent actions are output as the last two items in the printout, History and Events. Figure is a snapshot of a sequence of History records.

Snapshot of History printout

Owner 11628 is GRANTed a lock on resource 11744. Owner 12056 ENQueues a request for the same resource, asking to get it with no wait. The lock held by owner 11628 is in an incompatible mode, so that request is denied (DENY). Owner 12056 comes back with a different ENQueue request, asking for the lock again, but willing to wait. The Lock Manager POSTs pokes a notification to owner 11628 on the subject of resource 11744. Owner 12056 is told to WAIT. After 10 seconds, owner 12056 is still waiting, so the lock manager starts a deadlock SCAN. That yields nothing, so the lock manager goes back to poking owner 11628 (POST, POST, POST). Eventually the pokes get through, 11628 gives up (DEQueues) the lock, and it is GRANTed to 12056.

The Events printouts deliver the same history information in a different format. Figure is a snapshot of a sequence of history records output in the Events part of the printout.

Snapshot of Events printout

On Classic server, an Event record like the “Active” one shown in the figure might be cause for concern. It indicates that one Classic server process got a mutex for access to the resource, wrote its owner ID into the lock header block, and then got killed while it still held the lock table acquisition. However, the secondary lock header block should have enough information to enable a new process to undo any actions left partly completed by the killed process.

Interactive Sampling

The interactive time-series lock activity reports that fb_lock_print generates with the –i switch measure Lock Manager performance. The layout is modeled after the UNIX “sar” (System Activity Reporter) utility. The output shown in Figure sampled every 4 seconds for ten intervals. The sampling depicted in Figure was generated on a Classic version of Firebird running four local processes with a significant number of conflicts:

Interactive sampling

1. acquire/s: Average number of attempts to acquire the lock table per second
2. acqwait/s: Average number of acquire attempts that were forced to wait each second
3. %acqwait: Percent of attempts that were forced to wait
4. acqrtry/s: Average number of retries following a spin wait for the lock table mutex per second (SMP machines only, in theory)
5. rtrysuc/s: Average number of successful retries per second