db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "StmtExecutionPlan" by SusanCline
Date Fri, 31 Mar 2006 18:51:11 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by SusanCline:
http://wiki.apache.org/db-derby/StmtExecutionPlan

New page:
The RUNTIMESTATISTICS attribute can be used to obtain information about the length
of the compile time and the execution time for each statement executed within
a connection and the statement execution plan itself.  The statement execution plan is
sometimes referred to as the Query Plan.

An overview for using RUNTIMESTATISTICS is available in the Derby documentation
in the Tunning Guide, under the Section "Working with RunTimeStatistics".

Intrepreting the information in the Statment execution plan depends on various
factors including the isolation level of the transaction, the type of result set
and the type of statement (insert, update, delete or select.)

Looking at the truncated output from the statement execution plan below one might 
ask how many rows were locked and how many rows are currently locked?

Note that this example is using an isolation level of read committed and is using
row level locking.

Source result set:
Index Scan ResultSet for TS_MYTAB using constraint PK_TS_MYTAB at read committed 
isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16 

Two of the parameters being reporting on here, Rows seen and Fetch Size do not relate
directly to the number of rows locked during the execution of the statement.

For read committed, repeatable read and serializable Derby obtains a lock on every row that
it looks at.  For serializable, Derby actually obtains one more lock on the previous key to
protect a range of keys for phantom protection.  Depending on the isloation level, these locks
may or may not be held until the end of the transaction.

However, in the case of read committed, which the example above is using, the read locks are
released before the end of the transaction.  This means at any one time in a single table
scan there will be a value of zero or one for the number or locks outstanding.

For example in the scenario of a read committed isolation level using row-level locking with
a table containing 1000 rows, 1000 locks will have been obtained, but 999 of them will have
been released, and you will only be holding 1. This behaviour is true regardless of the fetch
size.

For additional information about the scope of locks see the Derby documentation:

http://db.apache.org/derby/docs/dev/devguide/rdevconcepts8424.html 

Mime
View raw message