1099 Why Use InterBase

1
1099Why Use InterBase?

• Bill Todd
• The Database Group, Inc.

2
Two Methods of Concurrency Control

• Locking
• Versioning

3
Locking Schemes

• Your locks restrict other users access to the
  locked object
• Lock granularity
• Database
• Table
• Page
• Row
• Index

4
Pessimistic Locking

• Row is locked before you begin changing it.
• Guarantees that you can commit your changes.
• Locks can persist for a long time which reduces
  concurrency.
• Larger number of locks increases load on database
  server.

5
Optimistic Locking

• Most common method among database servers.
• Lock is placed when row is updated and released
  when transaction commits.
• Locks exist for a shorter time.
• Another user can update the row while you are
  making changes.

6
Optimistic Locking Example 1

• John reads row and makes changes
• Jane reads row and makes changes
• John updates row causing lock
• Jane attemps update gets error

7
Optimistic Locking Example 2

• John reads row and makes changes
• Jane reads row and makes changes
• John updates row causing lock
• John commits releasing lock
• Jane updates row overwriting Johns changes
• Jane commits releasing lock

8
To Prevent Overwriting Changes

• Use UPDATE statement with original value of all
  fields in WHERE clause
• Does not work if only blob field is changed
• Use UPDATE that includes timestamp or counter
  field in WHERE clause
• Works for all field types

9
Transactions

• All changes within a transaction succeed or fail
  as a single unit

10
Transaction Isolation Level

• Your transactions isolation level controls when
  your transaction sees changes made by other
  transactions

11
SQL Standard Isolation Levels

• Read uncommitted
• Read committed
• Repeatable read
• Serializable

12
Locking Concurrency

• Repeatable read isolation requires locks to stop
  other users from updating rows you have read
• Serializable requires locks to stop other users
  from updating rows you have read or inserting new
  rows

13
Locking Example

• Inventory valuation by warehouse
• User A starts valuation query
• User B moves material from warehouse 1 to
  warehouse 20 while user As query is reading
  warehouse 10s records
• User As query counts the material twice.

14
Solution

• Use serializable isolation for the valuation
  query
• Problem - User B cannot update rows until the
  valuation query ends

15
Versioning

• Each transaction gets a unique number
• Each row version contains the number of the
  transaction that created it

16
Versioning Example

• Inventory valuation by warehouse
• User A starts valuation query
• User B moves material from warehouse 1 to
  warehouse 20 while user As query is reading
  warehouse 10s records.
• Because there is an active transaction with a
  lower number new row versions are created for all
  rows that are updated

17
Versioning Example (continued)

• User As transaction only reads the rows that
  were committed at the time it started
• The transaction gets the correct count
• User B is not blocked

18
Snapshot Isolation Level

• Unique to InterBase
• All the benefits of serializable
• Does not place any locks
• Does not block updates by others

19
How Versioning Works

• When a snapshot transaction starts it gets
• The next transaction number
• A copy of the Transaction Inventory Pages (TIP)

20
The TIP

• Shows the state of all transactions
• Active
• Committed
• Rolled back
• Limbo
• Only occurs if a two-phase commit fails

21
Which Row Version is Read?

• The most recent version whose transaction was
  committed at the time the reading transaction
  started is the version that is read

22
Example

• Transaction 90 reads a row with the following
  versions
• Tran 100 status committed
• Tran 80 status active when reader started
• Tran 60 status rolled back
• Tran 40 status committed when reader started

23
How Locking Versioning Compare

• A series of examples
• What happens with no concurrency control
• What happens with locking
• What happens with versioning

24
Example 1

• Husband and wife go to two different ATMs at the
  same time to withdraw money from their checking
  account.

25
Ex 1 No Concurrency
26
Example 1 - Locking
27
Example 1 - Versioning
28
Ex 2 No Concurrency
29
Example 2 - Locking
30
Example 2 - Versioning
31
Ex 3 No Concurrency
32
Example 3 - locking
33
Example 3 - Versioning
34
Ex 4 No Concurrency

• SELECT with a WHERE clause
• Another user inserts a row that satisfies the
  WHERE clause
• Rerun the query and you see the new row

35
Example 4 - Locking

• Place a table lock to prevent the insert
• Place an index range lock to prevent the insert

36
Example 4 - Versioning

• The insert is allowed
• The second SELECT does not see the inserted row
  because its transaction number is higher than
  that of the reading transaction

37
Example 5

• John and Jane are told to make their salaries
  equal

38
Ex 5 No Concurrency
39
Example 5 - Locking
40
Example 5 - Versioning
41
Example 5 Versioning Fix
42
How Do They Compare

• Versioning provides better concurrency in most
  cases involving mixed reads and writes

43
Recovery

• Database server must roll back all active
  transactions on restart after a crash
• For locking database this means processing
  transaction log
• For versioning only the TIP must be updated
• Status bits for all active transactions changed
  from Active to Rolled Back
• Garbage collection cleans up rolled back row
  versions

44
Garbage Collection

• Record versions cause database to grow
• Old versions are deleted each time a row is
  accessed
• A sweep removes all row versions that are no
  longer needed

45
InterBase Features

• Transaction support
• Read committed isolation
• Snapshot isolation
• Benefits of serializable isolation without
  blocking updaters
• SQL 92 entry level support with extnesions
• User define functions
• Cost base query optimizer

46
InterBase Features

• Row level locking
• No lock escalation
• No conversion deadlocks
• Two phase commit
• Multi-user and single user versions
• SMP support
• Hyperthreading support

47
InterBase Features

• Triggers
• Before
• After
• Old new values available
• Multiple triggers per event
• Control of trigger firing order
• Post events to clients from triggers
• Make any view updateable with triggers

48
InterBase Features

• Stored procedures
• Temporary tables
• ON COMMIT PRESERVE DELETE
• ALTER TABLE allows changing ON COMMIT clause
• Generators for incrementing keys
• Null state for all data types
• Declarative referential integrity

49
InterBase Features

• Server level security
• Database level security
• The levels can be mixed on a single server
• On-line backup
• On-line metadata changes
• Instantaneous crash recovery
• Replication

50
InterBase Features

• Zero maintenance
• Small disk footprint
• Small memeory footprint
• Does not require certified DBA
• Does not require custom installer
• Multi-platform support

51
IB vs. Oracle DB2

• Much less expensive
• Less complex easier to learn
• Easier to deploy
• Does not require highly trained DBA
• Much lower resource requirements
• Memory
• Disk
• CPU

52
IB vs. Microsoft SQL Server

• Less expensive
• Less complex easier to learn
• Easier to deploy
• No lock escalation
• No conversion deadlocks
• Does not require highly trained DBA
• Lower resource requirements
• Multi-platform support
• Instant crash recovery

53
IB vs. Advantage DBISAM

• Snapshot isolation for stable view of data
  without blocking updates
• On-line backups
• Shadowing
• Transactions can span databases
• Triggers can post events to clients
• Complex views are updateable with triggers

54
Applications Made For IB

• Mixed long reads and updates
• Embedded applications
• Widely deployed applications
• No on-site support
• Multi-platform applications

55
Questions?
56
Thank You

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