CS8630 Database Administration Dr. Mario Guimaraes. 10-23-2008, DB Admin, Concurrency and Recovery. Class Will Start Momentarily…. Database Administration. All large and small databases need database administration
where city < > ‘Miami’;
Implement the concept of a transaction. Command means end of transaction (releases all locks).
Transaction: a set of operations where all has to be executed or none.
Example: transfer $500 money from checking
ChkAccount = ChkAccount – 500
SavAccount = SavAccount + 500
When you issue a shared lock on a record, nobody else can update that recordExclusive x Shared Lock (review)
Personal DBMS normally offer less options and usually only lock by bigger units (table or lock the whole Database, for example).Unit of Locking - review
Set bal = bal – 500
Where aid = ‘A1’;
Set bal = bal + 500
Where aid = ‘A2’;
Select bal into :bal1 from accounts where aid = ‘A1’;
Select bal into :bal2 from accounts where aid = ‘A2’;
If (:bal1 + :bal2 < 500)
deny creditIf 2 transactions with no locks, what will happen ?
Select * from customers where cid = ‘c006’ for update no wait;
This command can avoid deadlock, but it may abort transactions pre-maturally.
Assuming that customer c002 (cap database) was already locked (exclusive lock) by another transaction, what is going to happen ? In other words, which tables will be updated in the example below ?
// BEGIN TRANSACTION
EXEC SQL WHENEVER SQLERROR DO sql_error ()
EXEC SQL UPDATE AGENTS …
EXEC SQL UPDATE ORDERS …
EXEC SELECT name, discnt INTO :cust_name, :cust_discnt FROM CUSTOMERS where cid = ‘c002’ FOR UPDATE NO WAIT;
UPDATE PRODUCTS …
// END TRANSACTION
exec sql rollback;
What happens when c002 is locked by another transaction ??
V$LOCK’s main attributes
sid (identifying the session holding or
WHERE username = ‘JOHN’;
USERNAME SID SERIAL # STATUS
JOHN 9 3 5
ALTER SYSTEM KILL SESSION ‘9,3’;