Chapter Twenty Two DATABASE TRANSACTIONS

1
Chapter Twenty Two DATABASE TRANSACTIONS

• Objectives
• - Controlling transactions
• - Advantages of COMMIT and ROLLBACK
• - Read Consistency
• - Locks
• - Native Dynamic SQL

2
Database Transactions

• DML
• DDL
• DCL
• Transaction starts with an execution of SQL
  statements and ends with one of the following
• COMMIT or ROLLBACK
• DDL or DCL (automatic commit)
• user exits or quits
• system crashes

3
Controlling Transactions

• An automatic commit occurs under the following
  circumstances
• DDL statement is issued
• DCL statement is issued
• normal exit from SQL, without explicitly issuing
  COMMIT or ROLLBACK
• Abnormal Termination

4
COMMIT ROLLBACK

• INSERT, DELETE, UPDATE data from a database can
  be reversed (ROLLBACK) or committed (COMMIT)
• Show AUTOCOMMIT
• ROLLBACK
• COMMIT
• SET AUTOCOMMIT ON

5
State of Data Before COMMIT or ROLLBACK

• The previous state of the data can be recovered
• The current user can review the result of the DML
  operation by using SELECT
• Other user cannot view the result of the DML
  statements by the current user
• The affected rows are locked (other users can not
  change the data within the affected rows)

6
State of the Data after COMMIT

• Data changes are made permanent in DB
• The previous state of the data is permanently
  lost
• All users can view the results
• Locks are released
• All saved points are erased

7
Committing Data

• UPDATE student
• SET major COSC
• WHERE id 111
• COMMIT

8
State of the Data after ROLLBACK

• Data changes are undone
• Previous state of the data is restored
• Locks on the rows are released
• DELETE FROM student
• 10,000 records are deleted
• ROLLBACK

9
Controlling Transactions
Transaction
Point B
Point A
COMMIT
Rollback
Rollback
Rollback
Rollback
10
Roll Back Changes to a Marker

• SAVEPOINT PointA
• ..
• SAVEPOINT PointB
• ..
• ROLLBACK TO PointB
• ROLLBACK

11
Roll Back Changes to a Marker

• Create a marker in a current transaction by
  using
• SAVEPOINT statement
• Rollback to that marker by using
• ROLLBACK TO statement

12
Roll Back Changes to a Marker

• BEGIN
• UPDATE faculty
• SET salary100000
• WHERE id111
• SAVEPOINT to_here
• INSERT INTO faculty
• VALUES (John Smith, 111, 25000)
• ROLLBACK TO to_here
• END

13
QUESTION?

• What will happen to a recursive procedure with a
  SAVEPOINT?

14
Read Consistency

• Db users make two types of access to db
• read operations (SELECT)
• write operations (INSERT, UPDATE, DELETE)

15
Read Consistency

• Facts
• Db reader and writer are ensured a consistent
  view of the data
• Writers are ensured that the changes to the db
  are done in a consistent way
• Changes made by one writer does not disrupt or
  conflict with changes another writer is making

16
SET TRANSACTION to READ ONLY

• DECLARE
• Daily_Sales REAL
• Weekly_Sales REAL
• Monthly_Sales REAL
• BEGIN
• COMMIT
• SET TRANSACTION READ ONLY NAME (calculate
  sale)
• SELECT SUM(amt)
• INTO Daily_Sales
• FROM Sales
• WHERE dte SYSDATE
• SELECT SUM(amt)
• INTO Weekly_Sales
• FROM Sales
• WHERE dte gt SYSDATE-7
• SELECT SUM(amt)
• INTO Monthly_Sales
• FROM Sales
• WHERE dte gt SYSDATE-30

17
Locking

• What are locks
• Require no user action
• Implicit locking occurs for all SQL statements
  except SELECT
• Automatically use the lowest level of
  restrictiveness
• Are held for the duration of the transaction
• Have two basic modes
• -Exclusive
• -Shared

18
Overriding Default Locking

• LOCK TABLE student IN ROW SHARE MODE NOWAIT
• Syntax
• LOCK TABLE tableview IN lock_mode MODE
  NOWAIT
• Where lock_mode
• ROW SHARE
• ROW EXCLUSIVE
• SHARE UPDATE
• SHARE
• SHARE ROW EXCLUSIVE
• EXCLUSIVE

19
Row Locking Specific Table

• DECLARE
• CURSOR c IS
• SELECT s_name, d_name
• FROM student, dept, semester
• WHERE student_depdept.name AND.
• FOR UPDATE OF student.major

20
Native Dynamic SQL(NDSQL)

• What is NDSQL?
• Application of NDSQL

21

EXECUTE IMMEDIATE MODE

• EXECUTE IMMEDIATE string
• INTO variable1,variable2,
• USING IN OUT IN OUT bind variable..

22
Using EXECUTE IMMEDIATE

• EXECUTE IMMEDIATE ( INSERT INTO student (ID,
  GPA) VALUES(1111,3.5) )
• CREATE OR REPLACE PROCEDURE generic (input IN
  VARCHAR2) AS
• BEGIN
• EXECUTE IMMEDIATE (input)
• END
• generic (INSERT INTO student (ID, GPA) VALUES
  (1111,3.5))
• generic(CREATE INDEX my_index ON
  students(name))

23
EXECUTE IMMEDIATE Bind Argument

• EXECUTE IMMEDIATE DELETE FROM student WHERE
  Major Name USING dept

24
Bind Arguement

• DECLARE
• D NUMBER(7,2) 20000
• E NUMBER(2) 50
• ID NUMBER(3)111
• sql_stat VARCHAR2(100)
• staff_Rec EmployeeROWTYPE
• BEGIN
• EXECUTE IMMEDIATE CREATE TABLE staff (id
  NUMBER, pay NUMBER)
• sql_stat INSERT INTO staff VALUES(1, 2)
• EXECUTE IMMEDIATE sql_stat USING E, D
• sql_stat SELECT FROM staff WHERE id
  ID
• EXECUTE IMMEDIATE sql_stat INTO staff_Rec USING
  E
• EXECUTE em (INSERT INTO student VALUES (John
  Smith, 1111, 2.5, COSC))
• EXECUTE IMMEDIATE COMMIT

25
Example

• CREATE OR REPLACE PROCEDURE Delete_rows(
• Table_Name IN VARCHAR2,
• Condition IN VARCHAR2 DEFAULT NULL) AS
• Where_Clause VARCHAR2(100) WHERE
  Condition
• BEGIN
• IF Condition is NULL THEN
• Where_Clause NULL
• END IF
• EXECUTE IMMEDIATE DELETE FROM Table_Name
  Where_Clause)
• END
• /
• Delete_rows(student,id111)
• Delete_rows(student)

26
Parameter Modes

• CREATE PROCEDURE create_dept(
• D_No IN OUT NUMBER,
• D_Name IN VARCHAR2,
• Loc IN VARCHAR2) AS
• BEGIN
• SELECT dept_seq.NEXTVAL INTO D_No
• FROM DUAL
• INSERT INTO dept
• VALUES (D_No, D_Name, Loc)
• END
• /

27
Parameter Modes

• DECLARE
• plsql_block VARCHAR2(450),
• new_dept NUMBER(3)
• new_name VARCHAR2(15) into teck
• new_loc VARCHAR2(20) my hall
• BEGIN
• plsql_block BEGIN create_dept (a, b, c)
  END
• EXECUTE IMMEDIATE plsql_block
• USING IN OUT new_dept, new_name, new_loc
• END
• /

28

• List of your objects
• SELECT
• FROM USER_OBJECTS
• List of dependencies to and from objects
• SELECT
• FROM USER_DEPENDENCIES
• List of text source code for all objects you own
• SELECT
• FROM USER_SOURCE

29

• List of your triggers
• SELECT
• FROM USER_TRIGGERS
• List of the parameters in your procedures and
  functions of your schema.
• SELECT
• FROM USER_ARGUMENTS

30
WRAP CODES

• Wrap codes
• Readable ASCII ? Non readable ASCII
• WRAP INAMEinputfile.SQL