Transcript Understanding Transactions

Understanding Firebird
Transactions
Pavel Císař
IBPhoenix
Firebird Conference 2003
Transactions
Everyone can use them
Transactions 101
A
B
Transactions 101
Transaction processing is a general concept to
ensure integrity of any dynamic system in the
course of transition from one consistent state
to another consistent state.
Functional description
Atomicity
Consistency
Isolation
Durability
Atomicity
All transformations in the course
of transaction are treat as ONE,
impartible transformation.


Pivotal and most visible attribute.
Nesting and save points.
Consistency
When transaction ends (anyhow),
system must be in consistent state.


System could be in the inconsistent state
during transaction.
Depends on how „Consistent state“ is defined.
Isolation
Transactions are isolated from
each other, so they don‘t
mutually interfere.

Source of all trouble with transactions.
 Desirable for consistency
 Horrible for concurrency
Durability
After (and only after) the
transaction is successfully
completed, all transformations
are permanent.

Special meaning of commit (and rollback).
Implementation strategies
(Atomicity and Durability)

Changes held in memory, written on Commit ?

Changes immediately written to database, with
recovery information:
–
–
Outside the database (Transaction log)
Right in database (Row versions)
Isolation – Cardinal problem
In ideal isolation, all updates
are mutually exclusive with
any other operation
(including read).
Implementation strategies
(Isolation)

Transaction log
–

Locks (many types)
Data versioning
–
–
State of transaction
Locks (supporting method)
Transaction Isolation Levels



Defined by SQL standard.
Degree of give-and-take between consistency
and concurrency.
Bound to transaction.
SQL92 Transaction Isolation Levels
Read
Uncommitted
Read Committed (required)
Repeatable Read
Serializable (required)
Read Uncommitted





Also called „Dirty Read“
Very basic (update) consistency
Highest concurrency
Acceptable for transactions that do not read
data
Not supported by Firebird
Read Committed

Basic consistency
–
–
–

Good for
–
–

Cannot read uncommited changes
Non-repeatable reads
Phantom rows
„Change monitoring“ transactions
Transactions that do not depend on repeatable read (mostly
update)
Supported by Firebird with options:
–
–
Record versions – Pending changes do not block reads
No record versions – Compatible with SQL standard
Repeatable Read




Higher consistency, much lower concurrency
SQL Standard allows phantom rows
Suitable for reports and some calculations
No direct support in Firebird. Firebird has isolation level
called Snapshot (or Concurrency) that is similar to
Repeatable Read, but:
–
–
Do not allows phantom rows.
Do not guarantee that transaction can update data that have
been read.
Serializable




Total consistency at the cost of very poor
concurrency.
The only one that do not allow phantom rows.
Necessary for some calculations.
Supported by Firebird as Snapshot Table
Stability (or Consistency). You can get the
same result with Snapshot + Table reservation.
Main differences between SQL
standard and Firebird

SQL standard is defined along lock&log
implementation.
–
–
Rows locked by update/delete (or insert) could not
be read by other transactions.
This do not apply for Firebird, except for Read
Committed with „No record version“ option.
Rows locked by read are updateable by blocking
transaction.
This do not apply for Firebird, except for Snapshot
Table Stability or transactions with table reservation.
Collisions - SQL Isolation Levels
Read
Uncommitted
Read
Committed
Repeatable
Read
Serializable
Read
Uncommitted
Read Committed
Repeatable Read
Serializable
Read
Write
Read
Write
Read
Write
Read
Write
Write
U/D
pending
U/D
U/D
U/D
U/D/I
U/D/I
Read
pending
Write
U/D
Read
U/D
Write
U/D
Read
U/D/I
Write
U/D/I
Read
pending
pending
U/D
pending
U/D
U/D
pending
U/D
U/D/I
U/D/I
U/D
U/D
U/D/I
pending
U/D
pending
U/D
U/D
U/D/I
U/D/I
U/D
U/D/I
U/D/I
U/D/I
U/D/I
U/D/I
U/D/I
Collisions – Firebird Isolation
Levels
Read
Committed
Snapshot
Table Stability
Snapshot
Read
Read
Write
Snapshot Table
Stability
Write
Read
Write
U/D
U/D
U/D/I
U/D
Write
U/D
U/D
U/D/I
U/D
Read
U/D/I
U/D/I
Write
U/D
U/D
Read
Write
Snapshot
Read Committed
Read
U/D/I
U/D/I
U/D
„Optimistic locking“



System always knows better what must be
protected, and when.
All „pending“ changes are always protected.
All reads are protected from update for
Repeatable Read and Serializable (SQL
standard). Firebird protects only Snapshot
Table Stability or on demand (table
reservation).
Table reservation



Available for all Firebird isolation levels.
Tables are reserved for the specified access when the
transaction is started (prevent possible deadlocks and
update conflicts that can occur if locks are taken only
when actually needed).
Fine-grained access specification for individual tables:
–
–
–
–
Shared read: All others can read and update, most liberal
Shared write: All others except „Serializable“ can read and
update.
Protected read: All can only read.
Protected write: All except „Serializable“ can read, but only
blocking transaction can write.
Lock resolution


Wait
The default, transaction will wait until locked
resources are released. Once the resources
are released, the transaction retries its
operation.
No wait
Transaction returns a lock conflict error without
waiting for locks to be released.
MGA implementation I.

Record versions
–
–

Linked list starting from most recent version.
Each version contains transaction number.
Bitmap of Transaction States
–
–
–
Used to determine the visibility of particular version for a
transaction.
Snapshot transactions have a snapshot created when they
start.
Read Committed transactions use shared map with actual
states.
MGA implementation II.

Transaction state bitmap from OIT (not committed transaction)
•
•
•
•

Stored in database as Transaction Inventory Pages (TIP).
–

00 – Active
01 – Limbo
10 – Dead (rollback)
11 – Committed
1K page can hold up to 4016 transactions (4 transactions per byte *
(1024 bytes – 16 byte header – 4 byte next TIP page number))
On first db attachment:
–
–
Transaction state bitmap is restored from TIPs
Active transactions are marked as dead
MGA implementation III.
T-Shirt Question!
Current Transaction States
Chain of row
versions
60 – Active
RC
59 – Active
S
58 – Rollback
S
57 – Commit
RC
60
56 – Commit
RC
56
55 – Active
S
54 – Rollback
RC
53 – Active
RC
52 – Commit
S
51 – Commit
RC
50 – Rollback
S
52
50
Oldest Active Transaction (OAT)
Oldest Interesting Transaction (OIT)
32
Transaction 53
will get version
56
MGA implementation IV.



New row version is always written in the place
of old version.
Previous version is moved to another place,
preferably on the same page.
When there is no more room on Data page for
previous version or new version, everything
starts to deteriorate.
Data page
Page header
Offset_2,
Size_2
Page
number
Offset_3,
Size_3
Relation
Number
…
Offset_1,
Size_1
Offset_N,
Size_N
Data - row N
Data - row 3
N
Data - row 2
…
Data - row 1
MGA implementation V.


All versions from dead transactions or versions from
transactions beyond first committed transaction lower
than OAT could be removed.
Unnecessary versions are detected whenever Firebird
access a row. This process is called Garbage
Collection.
–
–


Classic removes unnecessary versions immediately.
Super Server marks them for separate GC thread.
Garbage collection doesn't ensure that all row versions
from dead transactions are removed.
Long-running transactions are a disaster.
MGA implementation VI.


Commit/Rollback Retaining doesn't really ends
the transaction.
Rollback requested by client may use an undo
log:
–
–

If undo log is not too big.
If undo log is not disabled by client.
Read Only transactions in Read Committed
isolation level do not block garbage collection.
MGA implementation VII.

Save points
–
–

Have been there for long time for internal use
Modified and surfaced for general use in Firebird 1.5
SAVEPOINT name
ROLLBACK [WORK] TO [SAVEPOINT] name
Undo log for save point
–
–
–
–
Starts at transaction level
Created for statements, stored procedures and triggers
Nested when necessary and merged up
Much improved in Firebird 1.5 (use of B+tree)
MGA implementation VIII.

„Frozen“ Oldest Interesting Transaction
–
–



Slow down start of new transactions
Eats server resources
Gap between OIT and current transaction may start the
Sweep process (depend on sweep interval settings)
Sweep does the garbage collection of all unnecessary
versions in database + advance OIT
Full database GC (by gbak for example) do not replace
sweep!
MGA implementation IX.
Explicit record locking
–
–
–
–
–
New in Firebird 1.5
SELECT <...> [FOR UPDATE [OF col [, col ...]]
[WITH LOCK]]
For use with server-side cursors only
Dosn‘t mess with record versions like „fake update“
Use at your own risk
Firebird tips & tricks







Avoid use of Commit Retaining and stable db_keys.
Avoid designs that require many changes to single row.
Avoid designs that require updates as part of insert.
If transaction reads many rows for later update, use
table reservation.
For long-running „change monitoring“ transactions, use
Read Committed with Read Only attribute, or events.
Do not perform many changes in nested procedures.
Keep number of changes per transaction reasonably
small.
That‘s all (for now)
http://www.ibphoenix.com
We have answers