Lab week 10

1
Lab week 10

• Test lockingWrite a cursor

2
Setup a test for Locking

• Get together with members of your class.
• From your schema
• Create a table and populate it using the script
  SetupTable.sql found in the student distrib
  directory.
• Run setuptable.sql.

3
Grant Access

• Grant select and update access to those who are
  working with you.
• Each will require select access to the student
  table and update access to the studentname field.
• See GrantTable.sql (following slide) for an
  example

4
GrantTable.sql

• In the example, I am using the haulage system to
  set up the tables and the builder schema as Role
  A.
• From the Haulage schema, I ran the following
• grant select on student to builder
• grant select on stage to builder
• grant update on student(studentname) to builder
• Take roles A and B.

5
Role A

• Select studentname, stage_code from
  .student for update
• Just leave it like this until Role B is in a wait
  state.

6
My example A (logged in as haulage)
7
Role B

• Wait until Role A has done its work.
• Select studentname, stage_code from
  for update where studentname like Carol
  Britton
• Observe the result.
• When your system goes into a wait state, ask Role
  A to commit.

8
My example B (logged in as builder)
If this is done properly, the Bs session will
suspend.
9
Role A again

• Wait until Role B has done its work.
• Type in Commit
• Observe what happens to Role B.
• B should continue, but B has not committed.
• Select the row from A.
• Even though B has updated, the consistent read
  will give the pre-lock state of the system.

10
Cursor

• Write a PL/SQL block using a cursor, to display

John Flaherty ordered 20 Cavity blocks(100) on
02-FEB-05 John Browne ordered 20 Red bricks(100)
on 06-FEB-05 Mary Martin ordered 14 Window Frames
2'x4' on 12-FEB-05 Mary Martin ordered 47 Window
Frames 2'x4' on 04-FEB-05 Mary Martin ordered 1
6" Nails(50) on 04-FEB-05 Mary Martin ordered 3
6" Nails(100) on 04-FEB-05 Mary Martin ordered 40
Workbench on 04-FEB-05 Aidan Bourke ordered 1
Workbench on 06-FEB-05 Brad Pitt ordered 4
cordless Drill on 15-NOV-05 Brad Pitt ordered 4
cordless Drill on 15-NOV-05 Brad Pitt ordered 4
cordless Drill on 15-NOV-05 Etc