OLTP in Oracle
while using OLTP in Oracle 10g stanadard edition, does row level locking happen? What are its benefit?
Software/Hardware used:
Software/Hardware used:
ASKED:
April 20, 2010 9:23 AM
UPDATED:
April 21, 2010 2:30 PM
Answer Wiki:
The answer could be different depeding on the operation being performed. In Oracle there are many different types and levels of locks, and some operations would cause one type or level of locks while other operations would cause a totally different set of locks.
In general, there are two basic types of locks, which are 'exclusive' and 'share'.
Exclusive locks are obtained to modify data, and only one transaction can put an exclusive lock on a resource at the same time. In common DML operations such as UPDATEs, exclusive locks occur at the row level.
Share locks are less restrictive, and many different transactions/users can put a share lock on the same resource at the same time.
These types of locks are not mutually exclusive. Some operation could put exclusive locks at the row level, and a shared lock at the table label.
The benefit of row-level locking ? Higher degree of data concurrency .
Here's a better and more detailed explanation:
<a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#i5704">How Oracle Locks Data</a>
To see all answers submitted to the Answer Wiki: View Answer History.
As you are comparing Oracle and SQL Server, I think it is pertinent to mention that Oracle 10g never escalates locks (SQL Server does escalate locks, but it can be disabled). This could be an advantage or a disadvantage, but it is something you might want to know.
It is advantage for which database? How does escalating locks help?
Yes I am trying to compare Oracle and SQL can u help?
For example, if some update operation is affecting a big number of rows, SQL Server can decide to put a lock on the complete table instead of locking individual rows or pages. That is lock escalation.
Escalating locks will improve performance as fewer locks are created, but it will affect concurrence. If lock escalation does not occur, you will have better concurrence, but performace could be affected because of a higher number of locks.
So, lock escalation could be seen as an advantage (performance) and as a disadvantage (concurrence).