OLTP in Oracle

150 pts.
Tags:
OLTP in Oracle
Oracle 10g
Row Level Security
while using OLTP in Oracle 10g stanadard edition, does row level locking happen? What are its benefit?
ASKED: April 20, 2010  9:23 AM
UPDATED: April 21, 2010  2:30 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    65,110 pointsBadges:
    report
  • Akansha
    It is advantage for which database? How does escalating locks help? Yes I am trying to compare Oracle and SQL can u help?
    150 pointsBadges:
    report
  • carlosdl
    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).
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following