|
Implementing Locking
Understanding the Lock mechanism of your database has critical importance since if you use it improperly. Locks can inhibit data concurrecy. If your database, Locks data unncessarily
then concurrent operations will be very difficult to do.(Or you can not do.)
Therefore regardless of the database you use, You should understand your Lock system.
Building application is one step and the other step is (if necessary) porting Applications from one database to another database. In this case you are required to know Both databases' Locking System.
In this Section I will summarize the Oracle database Lock mechanism with an example.
-Oracle Locks on the row level on modifcation only.
-Reader cannot lock data and Reader cannot be blocked.(Reads are not blocked by writes.)
One block occurs when writer tries to modify row while the other writer tries to access data.
For example in a room reservation example.
When you want to reserve room by inserting data on schedules table
you can not use: select count(*) from schedules where resource_name = :room_name
and (start_time <= :new_end_time)
AND (end_time >= :new_start_time)
Since if you use this query than when two person tries to reserve room at the same instant then they will
reserve the same room. Therefore one room will have two guests. In Oracle You should use For Update
clause at the end of the restriction clause than it will block the other writer(in this case other customer who wants to reserve the room)
But the above query can work well in the other databases which uses table-level or page-level locking
But they have very big side effects since the other writers will be blocked in the case of When a single
change in a big table occurs.This will effect performance dramatically in the negative way.(Assume table has 10000 elements. And There are 1000 queries that each wants to achieve different elements of the same table. )
|
