Transcript Record Locking

Allowing Multi-user Access
• Grant
– GRANT <priv> ON <object> TO <user>
– |WITH GRANT OPTION / WITH ADMIN OPTION|
– <user> can be PUBLIC or a role
– <priv> can be all
• Revoke
– REVOKE <priv> ON <object> FROM <user>
• Very effective when used with views
Oracle Object Privs
•
•
•
•
•
•
•
•
ALTER
DELETE
EXECUTE
INDEX
INSERT
REFERENCES
SELECT
UPDATE
Problems with Concurrent Access
• Problems:
– Lost updates
– Uncommitted Data
– Inconsistent retrievals
• Solution
– Resource Locking
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.
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
ODBC and Access Record Locking
When you edit data in a linked SQL database
table using ODBC, Microsoft Access doesn't
lock records; instead, the rules of that SQL
database govern locking. In this instance,
regardless of the record-locking setting you
choose for your database, Microsoft Access
always acts as though the No Locks setting has
been selected.
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 update NOWAIT;
Where to specify locking in Access
• Advanced tab of tools\options to Set default
• RecordLocks property
– Forms. Specifies how records in the underlying table or
query are locked when data in a multiuser database is
updated.
– Reports. Specifies whether records in the underlying
table or query are locked while a report is previewed or
printed.
– Queries. Specifies whether records in a query (typically
an action query in a multiuser database) are locked
while the query is run.
• DAO. LockEdits property of recordset
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.
Example:
01/01/02
Journal Entry #87543
110 Accounts Receivable – R. Smith(123)
401 Service Revenue – tax services
402 Service Revenue – consulting services
(provided services on account)
Would cause multiple related inserts and updates
1,045
645
400
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;
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.
Transaction Methods (Access)
• BeginTrans begins a new transaction.
• CommitTrans ends the current transaction and
saves the changes.
• Rollback ends the current transaction and restores
the databases in the Workspace object to the state
they were in when the current transaction began.
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;
Transaction Methods (Access)
Sub PostAccounts()
‘Dim and assign variables including tempws, tempdb, temprs, posting
Posting = False
On Error GoTo ErrorCondition
tempws.BeginTrans
' Start of transaction.
Posting = True
‘complex set of operations here
Posting = False
tempws.CommitTrans
Alldone
‘release variables
ErrorCondition:
MsgBox "Error!"
If Posting Then
tempws.Rollback
End If
Resume Alldone
End Sub
Transaction/Locking Problem
• Problem:
– Deadlock
• Solutions
– Prevention
• e.g.: Two-phase locking with nowait
– Dectection
• Detect then kill and rollback one transaction
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;
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;
Access Record Selector Icons