1096 Understanding InterBase Transactions

1
1096Understanding InterBase Transactions

• Bill Todd
• The Database Group, Inc.

2
What is a Transaction

• Atomicity
• Consistency
• Isolation
• Durability

3
Atomicity

• All changes to all tables made within the
  transaction must succeed or fail as a single
  atomic unit

4
Consistency

• Database will always be left in a logically
  consistent state
• On restart after a crash all active transactions
  are automatically rolled back
• The database can never contain changes made by a
  transaction that did not commit

5
Isolation

• Your transaction cannot see changes made by other
  transactions that have not committed

6
Durability

• After a transaction commits the changes are a
  permanent.
• The changes cannot be lost or undone.

7
Transaction Isolation Level

• Your transactions isolation level controls when
  it will see changes made by other transactions

8
SQL Standard Isolation Levels

• Read uncommitted (dirty read)
• Read committed
• Repeatable read
• Serializable

9
Read Uncommitted

• Your transaction can see changes made by other
  transactions that have not committed
• Most databases do not support read uncommitted

10
Read Committed

• Your transaction can see changes made by other
  committed transactions

11
Repeatable Read

• If you execute the same SELECT more than once
  within your transaction you will get the same
  value for each row returned the first time in
  each subsequent result set
• You will also get any new records that were
  inserted after the prior execution of the SELECT

12
Serializable

• If you execute the same SELECT more than once
  within your transaction you will get the same
  result set each time

13
InterBase Isolation Levels

• Snapshot ANSI serializable
• Read committed

14
How Versioning Works

• All data access takes place within a transaction
• Each transaction is assigned a unique number
• Each row version has the number of the
  transaction that created it

15
Transaction Inventory Pages

• TIP tracks the state of all interesting
  transactions
• Transaction can have four states
• Active
• Committed
• Rolled back
• Limbo

16
Starting a Snapshot Transaction

• Transaction gets a copy of the TIP
• TIP copy used to determine the state of other
  transactions at the moment the snapshot
  transaction started

17
Starting a Read Committed Transaction

• Gets pointer to the TIP cache (TPC)
• TPC pointer used to determine the current state
  of other transactions

18
Updating a Row

• Check the TIP to see if there are active
  transactions with a lower transaction number
• If yes, create a new row version
• If no, update the existing row

19
Reading a Row - Snapshot

• Most recent version
• Committed at time the reading snapshot
  transaction started
• When a snapshot transaction reads it ignores all
  row versions committed after it started

20
Tran 90 Reads Row 123
21
Transaction Options

• Access Mode
• Lock resolution
• Table reservation

22
Access Mode

• Transactions can be read only or read/write
• Default is read/write
• Read only transactions have lower overhead
• Read only read committed transactions do not stop
  garbage collection

23
Setting Access Mode With IBX

• For a read only transaction add the word read
  without the quotes to the IBTransaction Params
  property
• For a read/write transaction add write to the
  Params property

24
Lock Resolution

• Wait
• A transaction with the wait option that tries to
  update a row your transaction has locked will
  wait until your transaction ends
• NoWait
• The transaction will raise an exception when it
  tries to update a locked row

25
Table Reservation

• Allows your transaction to lock tables when it
  starts
• This guarantees your transaction access to all of
  the tables it needs

26
Table Reservations
27
IBTransaction.Params

• Snapshot transaction reserving EMPLOYEE for
  protected, lock_read
• Table name is case sensitive

concurrency nowait protected lock_readEMPLOYEE
28
IBTransaction.Params

• A read committed transaction that reserves
  EMPLOYEE for shared, lock_read

read_committed nowait shared lock_readEMPLOYEE
29
Reserving Multiple Tables
concurrency nowait protected lock_readEMPLOYEE
shared lock_readSALARY_HISTORY
30
IBTransaction Params KeywordsIsolation Level
31
IBTransaction Params KeywordsAccess Mode
32
IBTransaction Params KeywordsLock Resolution
33
Keywords Isolation Level
34
Ending a Transaction

• Commit changes the transactions state on the
  TIP from active to committed
• Rollback can degrade perfomance
• If lt 100,000 changes IB undoes changes and
  commits
• If 100,000 changes or more IB changes state on
  the TIP from active to rolled back

35
Next Transaction

• The number that will be assigned to the next
  transaction that starts

36
OIT

• Oldest Interesting Transaction
• The oldest transaction whose state is not
  committed
• Oldest Active Transaction
• Oldest rolled back transaction
• Oldest limbo transaction
• Normally OIT OAT and they advance when the OAT
  commits

37
What Makes the OIT Stick

• Rollback with gt 100,000 changes
• Rollback of transactions that were active when
  the server crashed
• Transaction stuck in limbo

38
OST

• Oldest Snapshot Transaction
• The lowest number that appears in the
  Oldest_Snapshot of any active transaction

39
How Oldest_Snapshot Field Is Set

• Read only read committed Oldest_Snapshot is not
  assigned
• Read/write read committed Oldest_Snapshot
  Transaction
• Snapshot Oldest_Snapshot the oldest active
  read/write transaction

40
When the OST Moves

• When a new transaction starts
• When a commit retaining occurs
• When a sweep is run
• Note commit retaining on a snapshot
• Commits existing transaction
• Starts new transaction whose Oldest_Snapshot is
  same as the original transaction

41
Garbage Collection

• Removes row versions whose transaction is less
  than the OIT
• Occurs automatically when a row is accessed
• Occurs for all rows when a sweep is run

42
Sweep Interval

• Default OAT OIT gt 20,000
• Automatic sweep will only happen if the OIT gets
  stuck
• If the OIT is stuck due to a rollback a sweep
  will unstick it
• You can change the sweep interval using IBConsole
  or gfix

43
Sweep Interval in IBConsole
44
Sweep Interval Using gfix
gfix -h 10000 -user sysdba -password masterkey
employee.gdb
45
Fixing Limbo Transactions

• If the OIT is stuck because a transaction is in
  limbo you must fix it before sweeping
• You can fix limbo transactions automatically with
  gfix

gfix -two_phase -user sysdba -password masterkey
employee.gdb
46
Possible Problems

• OIT gets stuck
• OAT gets stuck

47
OIT Gets Stuck

• If OIT is stuck garbage collection stops
• Number of row versions increases
• Performance suffers
• Retrieving a row with many versions takes longer
• The TIP gets larger
• Database size increases

48
Stuck OIT Rare In InterBase 7.x

• Rollback a transaction with over 100,000 changes
• Rollback on restart after a server crash
• Crash during two_phase commit leaves a
  transaction in limbo

49
OAT Gets Stuck

• OAT gets stuck if a transaction is left active
• If OAT is stuck OIT is also stuck
• Not all active transactions stick the OAT in
  InterBase 7.1 SP1 and later

50
What Sticks the OAT?

• Read only read committed transactions can remain
  active indefinitely without sticking the OAT
• Read/write read committed transactions can remain
  active indefinitely if you call Commit Retaining
  after updating the database
• Snapshot transactions always stick the OAT

51
Savepoints

• Savepoint is a named point in a transaction that
  you can rollback to without rolling back the
  transaction

52
Creating a Savepoint
SAVEPOINT MY_SAVEPOINT Creates a savepoint named
MY_SAVEPOINT
53
Releasing a Savepoint
RELEASE SAVEPOINT MY_SAVEPOINT Releases the
named savepoint and frees Its resources
54
Rollback to a Savepoint
ROLLBACK TO SAVEPOINT MY_SAVEPOINT Rolls back
to MY_SAVEPOINT and continues the transaction
55
Transactions with isql

• Easy way to test transaction behavior
• Supports all transaction options
• Can open multiple sessions to simulate multiple
  users
• Use performance monitor to watch transactions
• See chapter 10 of the Operations Guide for
  information on isql

56
isql Command Line Options
57
Starting isql
isql -u sysdba -p masterkey employee.gdb
58
Transactions in isql

• Isql starts a transaction when it connects to a
  database
• Use COMMIT to end current transaction
• Use SET TRANSACTION to start a transaction

59
SET TRANSACTION options
60
SET TRANSACTION Isolation Level
ISOLATION LEVEL SNAPSHOT TABLE STABILITY
or READ COMMITTED RECORD_VERSION or READ
COMMITTED NO RECORD_VERSION
61
Example
SET TRANSACTION READ ONLY NOWAIT ISOLATION LEVEL
READ COMMITTED
62
Monitoring Transactions
63
A Transactions Attachment
64
Transaction Summary Info
65
Questions?
66
Thank You

• Please fill out the speaker evaluationYou can
  contact me further at bt2_at_dbginc.com

67
(No Transcript)