Allowing Multi-user Access

1. Allowing Multi-user Access • Grant • GRANT <objpriv> ON <object> TO <user> |WITH GRANT OPTION | • GRANT <sysPriv> TO <user> | WITH ADMIN OPTION| • <user> can be PUBLIC or a role • <priv> can be ALL • Revoke • REVOKE <objpriv> ON <object> FROM <user> • REVOKE <syspriv> FROM <user> • Very effective when used with views

2. Oracle Object Privs • ALTER • DELETE • EXECUTE • INDEX • INSERT • REFERENCES • SELECT • UPDATE

3. Problems with Concurrent Access • Problems: • Lost updates • Uncommitted Data • Inconsistent retrievals • Solution • Resource Locking

4. Locking Granularity • Database • Locks the entire database • Not efficient • Table • Locks one table • Sometimes needed for batch updates • Not very efficient • Page • Usually contains multiple rows • Locks a unit corresponding to a the working unit of the OS • Row • Locks one row of a table • Most commonly used locking level • Very efficient • Column • Locks only certain attributes of a row.

5. Lock Types and Strategies Exclusive vs Shared Locks (lock type) • Exclusive • Will not allow another transaction to obtain a lock of any kind. • Shared • Allows multiple transactions to share a read only lock on the data. • Will not allow any transaction to write the locked rows. • Can potentially be upgraded to an Exclusive lock Optimistic vs Pessimistic Locking (locking strategy) • Optimistic • No locking occurs until the transaction is ready for submission by the client program • Can use several techniques to avoid business rule violations • Check row image before updating (timestamp, sequence number, or compare fields) • Update only changed fields • Use incremental updates to balances • Use conditional transactions with a timer • Client refreshes (not typically an option on web applications) • Pessimistic • Rows are locked at point of client retrieval of data • Client application will not proceed if locks cannot be obtained • Locks are not released until client application submits completed transaction

6. Pessimistic Locking Strategy Steps Request Lock(s) Retry (wait) or Abort Lock(s) Acquired Read ABORT/rollback Ponder Update Commit/Release Lock(s)

7. Optimistic Locking Strategy Steps Read Ponder Request Lock(s) Retry (wait) or Abort Lock(s) Acquired ABORT/rollback Check for Mutation Mutation Detected Update * * update should include update to timestamp or mutation sequence number. Commit/Release Lock(s)

8. How to specify locking in Oracle • Table Lock Command • For update option of select command: Select * From Customer Where CustID = 123456 For update; Select * From Customer Where CustID = 123456 For updateNOWAIT; Select * From Customer Where CustID = 123456 For updateWAIT 5;

9. Transactions Transaction Definition: A logical unit of work that consists of one or more SQL statements that moves the database from one consistent state to another consistent state. Therefore, the transaction must succeed or fail as a unit. 01/01/02 Journal Entry #87543 110 Accounts Receivable – R. Smith(123) 1,045 401 Service Revenue – tax services 645 402 Service Revenue – consulting services 400 (provided services on account) Example: Would cause multiple related inserts and updates

10. Oracle: No Transaction Defined Begin Update accounts set balance = balance + 1045 where accountnum = ‘110’; Commit; Update accounts set balance = balance + 645 where accountnum = ‘401’; Commit; Update accounts set balance = balance + 400 where accountnum = ‘402’; Commit; Update customers set BalDue = BalDue + 1045 where custid = 123; Commit; Insert into JournalEntries (JENumber, JEDate, Description) values (87543, ‘01-JAN-02’, ‘provided services on account’); Commit; Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 1, ‘110’, 1045.00); Commit; Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 2, ‘401’, 645.00); Commit; Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 3, ‘402’, 400.00); Commit; End;

11. Transaction Methods (Oracle) • Begin optional • Savepoint <savepointname> optional,creates a point that can be rolled back to • Commit [work] ends the current transaction and saves the changes. • Rollback [to <savepointname>] ends the current transaction and restores the databases in the Workspace object to the state they were in when the current transaction began.

12. Transaction Methods (Oracle with lock waits) Begin Begin Transaction; Update accounts set balance = balance + 1045 where accountnum = ‘110’; Update accounts set balance = balance + 645 where accountnum = ‘401’; Update accounts set balance = balance + 400 where accountnum = ‘402’; Update customers set BalDue = BalDue + 1045 where custid = 123; Insert into JournalEntries (JENumber, JEDate, Description) values (87543, ‘01-JAN-02’, ‘provided services on account’); Insert into DrCrDetail(JENumber, LineNumber, Account, Amount) values (87543, 1, ‘110’, 1045.00); Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 2, ‘401’, 645.00); Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 3, ‘402’, 400.00); Commit; Exception rollback; End;

13. Transaction/Locking Problem • Problem: • Deadlock • Solutions • Prevention • e.g.: Two-phase locking with nowait • Dectection • Detect then kill and rollback one transaction

14. Transaction Methods (Oracle with 2 stage locking) Begin Begin Transaction; Select * from accounts where accountnum in (‘110’, ‘401’, ‘402’) for update nowait; Select * from customers where custid = 123 for update nowait; Update accounts set balance = balance + 1045 where accountnum = ‘110’; Update accounts set balance = balance + 645 where accountnum = ‘401’; Update accounts set balance = balance + 400 where accountnum = ‘402’; Update customers set BalDue = BalDue + 1045 where custid = 123; Insert into JournalEntries (JENumber, JEDate, Description) values (87543, ‘01-JAN-02’, ‘provided services on account’); Insert into DrCrDetail(JENumber, LineNumber, Account, Amount) values (87543, 1, ‘110’, 1045.00); Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 2, ‘401’, 645.00); Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 3, ‘402’, 400.00); Commit; Exception rollback; End;

15. New SELECT FOR UPDATE WAIT <seconds> Begin Begin Transaction; Select * from accounts where accountnum in (‘110’, ‘401’, ‘402’) for update wait 3; Select * from customers where custid = 123 for update wait 3; Update accounts set balance = balance + 1045 where accountnum = ‘110’; Update accounts set balance = balance + 645 where accountnum = ‘401’; Update accounts set balance = balance + 400 where accountnum = ‘402’; Update customers set BalDue = BalDue + 1045 where custid = 123; Insert into JournalEntries (JENumber, JEDate, Description) values (87543, ‘01-JAN-02’, ‘provided services on account’); Insert into DrCrDetail(JENumber, LineNumber, Account, Amount) values (87543, 1, ‘110’, 1045.00); Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 2, ‘401’, 645.00); Insert into DrCrDetail (JENumber, LineNumber, Account, Amount) values (87543, 3, ‘402’, 400.00); Commit; Exception rollback; End;