CS8630, Summer/2004 – Test # 3ConcurrencyTRANSACTION 1 TRANSACTION 2CS8630, Summer/2004 – Test # 3Concurrency1) Users System and Scott have two different SQL sessions open. The following commands are executed from the SQL prompt in the order shown below.User: System User: ScottA) lock table scott.customers in exclusive mode;B) create or replace trigger trigafter insert on ordersfor each row begin lock table customers in exclusive mode nowait; update customers set discnt = discnt+11 where customers.cid=:new.cid;end;C) insert into orders (ordno, cid) values (1111, 'c001');D) rollback;Describe what is going to happen at steps C) and D) above.Describe what is going to happen if we remove the “lock table customers in exclusive mode no wait” line from the trigger.2) For the example below, what are the sequence of operations (assuming the Update generates anexclusive lock and the select statement generates a shared lock) ? a) ABDCE b) AEBCD c) AB and Deadlockd) ACDEB3) If transaction 1 issues the command “LOCK ACCOUNTS IN EXCLUSIVE MODE;” at the very beginning of the transaction (before A), what sequence of operations a) ABDCEb) AEBCDc) AB and Deadlockd) ACDEB4) Assume transaction attempts Step C (AccountNo = 10052) before step B (accountNo=10054). In other words the order of Steps B and C below are inverted. Also assume that Updates and Selects are generating implicit locks.and the “LOCK ACCOUNTS ..” statement was not performed. a) ABDCEb) AEBCDc) AB and Deadlockd) ACDEBACCOUNTSAcctNo Balance Type Ssn10052 1600.00 C 111.11.111110054 1200.00 S 111.11.1111…. …. .. …..TRANSACTION 1 TRANSACTION 2A) Update accountsSet balance = balance –1500 Where accountNo = 10052;B) Select balance into :v_balance1From accountsWhere accountNo = 10054;C) Select balance into :v_balance2 From accountswhere accountNo = 10052;D) If (:v_balance1 + v_balance2 < 2000)Print “Deny Credit Card”ElsePrint “Issue Credit”;Commit;E) Update accountsSet balance = balance + 1500Where accountNo = 10054;Commit;5) Which of the following statements is not true about locksa) When we lock a record or a table in shared mode, no other transaction can update the record or table, but other transactions can access the record.b) When we disconnect from an sql session through an exit command, there is an implicit commit (by default). c) In oracle an Update will lock in exclusive mode the records being updated.d) When we lock a record in shared mode, , no other transaction can read (select) or update the record or table that is locked.6) Which of the following mechanisms should an application programmer do to avoid deadlocks:a) lock all resources that the transaction will need at the beginning of the transaction with a NO WAIT clause and release all locks by committing just before user inputwhen the logic of the application permits.b) Kill everyone else’s session with an Alter System command.c) Make the end-users work at nightd) Use the FOR UPDATE clausee) Lock all resources using EXCLUSIVE MODE instead of SHARED MODE7) What is the best solution for a DBA to do if there is a deadlock ?a) re-boot the systemb) backup your database, drop your indexes and restore the databasec) see what sessions are running and kill all the sessions involved inthe deadlock except for oned) defragment the disk8) Which of the following is true about a power failurea) It keeps the updates already performed, but it will not complete the current commandb) It will always rollback the current transaction. The DDL and DML commands performed in the current transaction will be undone.c) It will always rollback the current transaction. Only DML commands performed in the current transaction will be undone.d) If there is error handling code, it will execute the error handling code. Otherwise, it will rollback the DDL and DML commands.e) If there is error handling code, it will execute the error handling code. Otherwise, it will rollback only DML commandsPL/SQL9) Assume the trigger below has been compiled successfully/create or replace trigger UpdateQuantityafter update on ordersfor each row begin update products set quantity = quantity + :old.qty - :new.qty where products.pid=:new.pid;end;After we issue the command “Update Orders set qty = 1000 where ordno = 1017; “Show what the trigger will do by modifying any values in one of the tables below: ORDERS ordno pid qty PRODUCTS pid pname quantity1000 p01 200 p01 Eraser 20001017 p02 500 p02 Pencil 30001020 p02 70010) Explain one good reason why one should divide the database into tablespaces.11) Identify line of code in both windows (PL/SQL and Java) that:A) Allocates the memory structure below: B) stores in the memory structure the result of the select statementC) Sets the next row as current (in the PL/SQL statement, it also moves the current row tovariables.12) For the animation in exercise 21,what is the data type of v_ssn in the PL/SQL window ?13) Below is a Java program that accesses an Oracle database. Describe all six parameters of the connection string (line in bold).a) jdbc:oracle:thin _____________________________________________b) localhost ___________________________________________________c) 1521 ___________________________________________________d) cl2009 ___________________________________________________e) Scott ___________________________________________________f) Tiger ___________________________________________________• import java.sql.*;• import java.io.*;• class Query {• public static void main (String args[])• throws SQLException, IOException {• try {• Class.forName("oracle.jdbc.driver.OracleDriver");• } catch (ClassNotFoundException e) {• System.out.println("Could not load the driver");• }• String user="Scott";• String pass="Tiger";• System.out.println (user + pass);• • Connection con=DriverManager.getConnection• ("jdbc:oracle:thin:@localhost:1521:cl2009","Scott","Tiger");• Statement stmt=con.createStatement();• ResultSet cust=stmt.executeQuery• ("Select cname, city, discnt FROM customers");• while (cust.next ()) {• System.out.println("CUstomer:"+cust.getString(1)+ " Discnt: "+cust.getString(2));• }• stmt.close();• con.close();• }• }14-18 SECURITY (MATHING)14. SECURING THE DATABASE ______ A. SQL INJECTION