Locking
It is important from database management system’s point of view to ensure that two user are
not modifying the same data at the time in a destructive manner.
Let us consider the following example to understand what will happen if two users are trying to update the same data at the same time.
Assume we have PRODUCTS table with details of products. Each product is having quantity on
hand (QOH). Transactions such as sales and purchases are going to modify QOH column of
the table.
The following are the steps that might take place when two transactions – one sale and one
purchase – are taking place.
1. Assume QOH of product 10 is 20 units.
2. At 10:00 USER1 has sold 5 units and updated QOH as follows but has not committed the
change. After UPDATE command QOH will be 15.
update products set qoh = qoh – 5 where prodid = 10;
3. At 10:11 USER2 has purchased 10 units and updated QOH as follows and committed. After
UPDATE command QOH will be 25 as 10 it added to 15.
update products set qoh = qoh + 10 where prodid = 10;
4. If at 10:12 USER1 has rolled back the UPDATE then data that was there before the
UPDATE should be restored i.e.20. But the actual data should be 30 as we added 10 units
to it at 10:11 and committed.
As you can see in the above example, if two users are trying to update the same row at the
same time the data may be corrupted. As shown in the above example at the end of the
process QOH should be actually 30 but it is not only 20.
It is possible to ensure that two transactions are interfering with each other by locking the
rows that are being modified so that only one transaction at a time can make the change.
Oracle ensures that only one transaction can modify a row at a time by locking the row once
the row is updated. The lock will remain until the transaction is completed.
Oracle also ensures that other users will not see the changes that are not committed. That
means if transaction T1 has updated a row then until the transaction is committed no other
transaction in the system will see the changes made by T1. Instead other transactions will see
only the data that was existing before the change was made.
The following scenario will illustrate the process in detail.
1. Assume QOH of product 10 is 20.
2. Transaction T1 has issued UPDATE command to update QOH of product 10. Oracle locks
the row that is updated and does not allow other transactions to update the row. However,
it is possible to read the data from the row.
update products set qoh = qoh + 5 where prodid = 10;
3. If T1 has issued SELECT to retrieve data then it will get 25 in QOH of product 10.
4. If T2 has issued SELECT command, it will see only 20 in QOH of product 10. This is
because no uncommitted changes will be available to other transactions.
5. If T2 is trying to update product 10 then Oracle will cause transaction T2 to wait until
transaction T1 (that holds lock on this row) is completed. Oracle will wait for lock to be
released indefinitely.
6. If transaction T1 is committed then change is made permanent and lock will be released.
Now it is possible for other transactions to see the updated data and even update the row
if required.
The following are the important points to be remembered about Oracle’s locking
mechanism.
* Locking in Oracle is automatic and transparent. That means we never need to ask Oracle
to lock row that is being modified. Locking is transparent means user is not aware of the
locking process. It happens automatically and in the background.
* Oracle locks the row that is being updated. That means locking is row-level. Other levels
that are in user are - page-level and table-level.
* Oracle releases locks held by a transaction when transaction is completed either
successfully – using COMMIT – or unsuccessfully – using ROLLBACK.
* If a transaction is trying to update a row and if row is already locked then Oracle will wait
for the row that is locked to be unlocked indefinitely. It is because of the fact that rows are
locked for a small duration in a typical production system. So Oracle prefers to wait to
cause any error.
* It is possible to lock table manually using LOCK TABLE command.
Locking the rows that are being updated is an important part of Oracle. It ensures that no two
transactions can update the same row at the same time. Locking mechanism is followed by all
database management systems. But some smaller database management systems follow
page-level locking where not the exact row that is being modified is locked instead the entire
page in which the row exists is locked.
It is important from database management system’s point of view to ensure that two user are
not modifying the same data at the time in a destructive manner.
Let us consider the following example to understand what will happen if two users are trying to update the same data at the same time.
Assume we have PRODUCTS table with details of products. Each product is having quantity on
hand (QOH). Transactions such as sales and purchases are going to modify QOH column of
the table.
The following are the steps that might take place when two transactions – one sale and one
purchase – are taking place.
1. Assume QOH of product 10 is 20 units.
2. At 10:00 USER1 has sold 5 units and updated QOH as follows but has not committed the
change. After UPDATE command QOH will be 15.
update products set qoh = qoh – 5 where prodid = 10;
3. At 10:11 USER2 has purchased 10 units and updated QOH as follows and committed. After
UPDATE command QOH will be 25 as 10 it added to 15.
update products set qoh = qoh + 10 where prodid = 10;
4. If at 10:12 USER1 has rolled back the UPDATE then data that was there before the
UPDATE should be restored i.e.20. But the actual data should be 30 as we added 10 units
to it at 10:11 and committed.
As you can see in the above example, if two users are trying to update the same row at the
same time the data may be corrupted. As shown in the above example at the end of the
process QOH should be actually 30 but it is not only 20.
It is possible to ensure that two transactions are interfering with each other by locking the
rows that are being modified so that only one transaction at a time can make the change.
Oracle ensures that only one transaction can modify a row at a time by locking the row once
the row is updated. The lock will remain until the transaction is completed.
Oracle also ensures that other users will not see the changes that are not committed. That
means if transaction T1 has updated a row then until the transaction is committed no other
transaction in the system will see the changes made by T1. Instead other transactions will see
only the data that was existing before the change was made.
The following scenario will illustrate the process in detail.
1. Assume QOH of product 10 is 20.
2. Transaction T1 has issued UPDATE command to update QOH of product 10. Oracle locks
the row that is updated and does not allow other transactions to update the row. However,
it is possible to read the data from the row.
update products set qoh = qoh + 5 where prodid = 10;
3. If T1 has issued SELECT to retrieve data then it will get 25 in QOH of product 10.
4. If T2 has issued SELECT command, it will see only 20 in QOH of product 10. This is
because no uncommitted changes will be available to other transactions.
5. If T2 is trying to update product 10 then Oracle will cause transaction T2 to wait until
transaction T1 (that holds lock on this row) is completed. Oracle will wait for lock to be
released indefinitely.
6. If transaction T1 is committed then change is made permanent and lock will be released.
Now it is possible for other transactions to see the updated data and even update the row
if required.
The following are the important points to be remembered about Oracle’s locking
mechanism.
* Locking in Oracle is automatic and transparent. That means we never need to ask Oracle
to lock row that is being modified. Locking is transparent means user is not aware of the
locking process. It happens automatically and in the background.
* Oracle locks the row that is being updated. That means locking is row-level. Other levels
that are in user are - page-level and table-level.
* Oracle releases locks held by a transaction when transaction is completed either
successfully – using COMMIT – or unsuccessfully – using ROLLBACK.
* If a transaction is trying to update a row and if row is already locked then Oracle will wait
for the row that is locked to be unlocked indefinitely. It is because of the fact that rows are
locked for a small duration in a typical production system. So Oracle prefers to wait to
cause any error.
* It is possible to lock table manually using LOCK TABLE command.
Locking the rows that are being updated is an important part of Oracle. It ensures that no two
transactions can update the same row at the same time. Locking mechanism is followed by all
database management systems. But some smaller database management systems follow
page-level locking where not the exact row that is being modified is locked instead the entire
page in which the row exists is locked.
No comments:
Post a Comment