Transactions

1
Transactions

• Serializability
• Isolation Levels
• Atomicity

2
The Setting

• Database systems are normally being accessed by
  many users or processes at the same time.
• Both queries and modifications.
• Unlike Operating Systems, which support
  interaction of processes, a DMBS needs to keep
  processes from troublesome interactions.

3
Example Bad Interaction

• You and your spouse each take 100 from different
  ATMs at about the same time.
• The DBMS better make sure one account deduction
  doesnt get lost.
• Compare An OS allows two people to edit a
  document at the same time. If both write, ones
  changes get lost.

4
ACID Transactions

• A DBMS is expected to support ACID
  transactions, which are
• Atomic Either the whole process is done or none
  is.
• Consistent Database constraints are preserved.
• Isolated It appears to the user as if only one
  process executes at a time.
• Durable Effects of a process do not get lost if
  the system crashes.

5
Transactions in SQL

• SQL supports transactions, often behind the
  scenes.
• Each statement issued at the generic query
  interface is a transaction by itself.
• In programming interfaces like Embedded SQL or
  PSM, a transaction begins the first time an SQL
  statement is executed and ends with the program
  or an explicit end.

6
COMMIT

• The SQL statement COMMIT causes a transaction to
  complete.
• Its database modifications are now permanent in
  the database.

7
ROLLBACK

• The SQL statement ROLLBACK also causes the
  transaction to end, but by aborting.
• No effects on the database.
• Failures like division by 0 can also cause
  rollback, even if the programmer does not request
  it.

8
An Example Interacting Processes

• Assume the usual Sells(bar,beer,price) relation,
  and suppose that Joes Bar sells only Bud for
  2.50 and Miller for 3.00.
• Sally is querying Sells for the highest and
  lowest price Joe charges.
• Joe decides to stop selling Bud and Miller, but
  to sell only Heineken at 3.50.

9
Sallys Program

• Sally executes the following two SQL statements,
  which we call (min) and (max), to help remember
  what they do.
• (max) SELECT MAX(price) FROM Sells
• WHERE bar Joes Bar
• (min) SELECT MIN(price) FROM Sells
• WHERE bar Joes Bar

10
Joes Program

• At about the same time, Joe executes the
  following steps, which have the mnemonic names
  (del) and (ins).
• (del) DELETE FROM Sells
• WHERE bar Joes Bar
• (ins) INSERT INTO Sells
• VALUES(Joes Bar, Heineken,
• 3.50)

11
Interleaving of Statements

• Although (max) must come before (min) and (del)
  must come before (ins), there are no other
  constraints on the order of these statements,
  unless we group Sallys and/or Joes statements
  into transactions.

12
Example Strange Interleaving

• Suppose the steps execute in the order
  (max)(del)(ins)(min).
• Joes Prices
• Statement
• Result
• Sally sees MAX lt MIN!

(ins)
13
Fixing the Problem With Transactions

• If we group Sallys statements (max)(min) into
  one transaction, then she cannot see this
  inconsistency.
• She sees Joes prices at some fixed time.
• Either before or after he changes prices, or in
  the middle, but the MAX and MIN are computed from
  the same prices.

14
Another Problem Rollback

• Suppose Joe executes (del)(ins), but after
  executing these statements, thinks better of it
  and issues a ROLLBACK statement.
• If Sally executes her transaction after (ins) but
  before the rollback, she sees a value, 3.50, that
  never existed in the database.

15
Solution

• If Joe executes (del)(ins) as a transaction, its
  effect cannot be seen by others until the
  transaction executes COMMIT.
• If the transaction executes ROLLBACK instead,
  then its effects can never be seen.

16
Isolation Levels

• SQL defines four isolation levels choices
  about what interactions are allowed by
  transactions that execute at about the same time.
• How a DBMS implements these isolation levels is
  highly complex, and a typical DBMS provides its
  own options.

17
Choosing the Isolation Level

• Within a transaction, we can say
• SET TRANSACTION ISOLATION LEVEL X
• where X
• SERIALIZABLE
• REPEATABLE READ
• READ COMMITTED
• READ UNCOMMITTED

18
Serializable Transactions

• If Sally (max)(min) and Joe (del)(ins) are
  each transactions, and Sally runs with isolation
  level SERIALIZABLE, then she will see the
  database either before or after Joe runs, but not
  in the middle.
• Its up to the DBMS vendor to figure out how to
  do that, e.g.
• True isolation in time.
• Keep Joes old prices around to answer Sallys
  queries.

19
Isolation Level Is Personal Choice

• Your choice, e.g., run serializable, affects only
  how you see the database, not how others see it.
• Example If Joe Runs serializable, but Sally
  doesnt, then Sally might see no prices for Joes
  Bar.
• i.e., it looks to Sally as if she ran in the
  middle of Joes transaction.

20
Read-Commited Transactions

• If Sally runs with isolation level READ
  COMMITTED, then she can see only committed data,
  but not necessarily the same data each time.
• Example Under READ COMMITTED, the interleaving
  (max)(del)(ins)(min) is allowed, as long as Joe
  commits.
• Sally sees MAX lt MIN.

21
Repeatable-Read Transactions

• Requirement is like read-committed, plus if data
  is read again, then everything seen the first
  time will be seen the second time.
• But the second and subsequent reads may see more
  tuples as well.

22
Example Repeatable Read

• Suppose Sally runs under REPEATABLE READ, and the
  order of execution is (max)(del)(ins)(min).
• (max) sees prices 2.50 and 3.00.
• (min) can see 3.50, but must also see 2.50 and
  3.00, because they were seen on the earlier read
  by (max).

23
Read Uncommitted

• A transaction running under READ UNCOMMITTED can
  see data in the database, even if it was written
  by a transaction that has not committed (and may
  never).
• Example If Sally runs under READ UNCOMMITTED,
  she could see a price 3.50 even if Joe later
  aborts.

24
SQL Authorization

• Privileges
• Grant and Revoke
• Grant Diagrams

25
Authorization

• A file system identifies certain privileges on
  the objects (files) it manages.
• Typically read, write, execute.
• A file system identifies certain participants to
  whom privileges may be granted.
• Typically the owner, a group, all users.

26
Privileges --- 1

• SQL identifies a more detailed set of privileges
  on objects (relations) than the typical file
  system.
• Nine privileges in all, some of which can be
  restricted to one column of one relation.

27
Privileges --- 2

• Some important privileges on a relation
• SELECT right to query the relation.
• INSERT right to insert tuples.
• May apply to only one attribute.
• DELETE right to delete tuples.
• UPDATE right to update tuples.
• May apply to only one attribute.

28
Example Privileges

• For the statement below
• INSERT INTO Beers(name)
• SELECT beer FROM Sells
• WHERE NOT EXISTS
• (SELECT FROM Beers
• WHERE name beer)
• We require privileges SELECT on Sells and Beers,
  and INSERT on Beers or Beers.name.

29
Authorization IDs

• A user is referred to by authorization ID,
  typically their name.
• There is an authorization ID PUBLIC.
• Granting a privilege to PUBLIC makes it available
  to any authorization ID.

30
Granting Privileges

• You have all possible privileges on the objects,
  such as relations, that you create.
• You may grant privileges to other users
  (authorization IDs), including PUBLIC.
• You may also grant privileges WITH GRANT OPTION,
  which lets the grantee also grant this privilege.

31
The GRANT Statement

• To grant privileges, say
• GRANT ltlist of privilegesgt
• ON ltrelation or other objectgt
• TO ltlist of authorization IDsgt
• If you want the recipient(s) to be able to pass
  the privilege(s) to others add
• WITH GRANT OPTION

32
Example GRANT

• Suppose you are the owner of Sells. You may say
• GRANT SELECT, UPDATE(price)
• ON Sells
• TO sally
• Now Sally has the right to issue any query on
  Sells and can update the price component only.

33
Example Grant Option

• Suppose we also grant
• GRANT UPDATE ON Sells TO sally
• WITH GRANT OPTION
• Now, Sally can not only update any attribute of
  Sells, but can grant to others the privilege
  UPDATE ON Sells.
• Also, she can grant more specific privileges like
  UPDATE(price) ON Sells.

34
Revoking Privileges

• REVOKE ltlist of privilegesgt
• ON ltrelation or other objectgt
• FROM ltlist of authorization IDsgt
• Your grant of these privileges can no longer be
  used by these users to justify their use of the
  privilege.
• But they may still have the privilege because
  they obtained it independently from elsewhere.

35
REVOKE Options

• We must append to the REVOKE statement either
• CASCADE. Now, any grants made by a revokee are
  also not in force, no matter how far the
  privilege was passed.
• RESTRICT. If the privilege has been passed to
  others, the REVOKE fails as a warning that
  something else must be done to chase the
  privilege down.

36
Grant Diagrams

• Nodes user/privilege/option/isOwner?
• UPDATE ON R, UPDATE(a) on R, and UPDATE(b) ON R
  live in different nodes.
• SELECT ON R and SELECT ON R WITH GRANT OPTION
  live in different nodes.
• Edge X -gtY means that node X was used to grant
  Y.

37
Notation for Nodes

• Use AP for the node representing authorization
  ID A having privilege P.
• P represents privilege P with grant option.
• P represents the source of the privilege P.
  That is, AP means A is the owner of the
  object on which P is a privilege.
• Note implies grant option.

38
Manipulating Edges --- 1

• When A grants P to B, We draw an edge from AP
  or AP to BP.
• Or to BP if the grant is with grant option.
• If A grants a subprivilege Q of P (say
  UPDATE(a) on R when P is UPDATE ON R) then the
  edge goes to BQ or BQ , instead.

39
Manipulating Edges --- 2

• Fundamental rule user C has privilege Q as long
  as there is a path from XQ (the origin of
  privilege Q ) to CQ, CQ , or CQ.
• Remember that XQ could be CQ.

40
Manipulating Edges --- 3

• If A revokes P from B with the CASCADE option,
  delete the edge from AP to BP.
• If A uses RESTRICT, and there is an edge from BP
  to anywhere, then reject the revocation and make
  no change to the graph.

41
Manipulating Edges --- 4

• Having revised the edges, we must check that each
  node has a path from some node, representing
  ownership.
• Any node with no such path represents a revoked
  privilege and is deleted from the diagram.

42
Example Grant Diagram
AP
A owns the object on which P is a privilege
43
Example Grant Diagram
A executes REVOKE P FROM B CASCADE
AP
BP
CP
CP
However, C still has P without grant option
because of the direct grant.