Hi Susie
Hmm!! I'll try to explain.
Locks a managed by the database, and you can acquire them implicitly or explicitly ( I prefer to lock the tables explicitly, update the tables and then release the locks, because then I know what's going on - I think I do anyway).
Locks are essential to multiuser app's because they guarantee consistency in the database. Often you don't have to worry about them, but a few rules apply:
1. Don't lock more than you have to (eg. read with the no-lock option whenever possible).
2. Keep the period you lock data as short as possible (eg. don't read with exclusive-lock before you have to, and read with no-lock whenever possible ).
3. Always lock tables in the same order.
There may be more ...
Deadlocks:
Consider an application where you have two programs that run simultaneously.
Program-1 updates TABLE-A and TABLE-B.
Program-2 updates TABLE-A and TABLE-B.
To prevent other programs from updating the tables while Program-1 is running, the program locks the tables
do transaction:
find first TABLE-A exclusive lock where .......
Do some data processing that takes a while.
find first TABLE-B exclusive lock where .......
Some more data processing
end. /* Transaction */
Program-2 also locks the tables, but in opposite order.
do transaction:
find first TABLE-B exclusive lock where .......
Do some data processing that takes a while.
find first TABLE-A exclusive lock where .......
Some more data processing
end. /* Transaction */
If the programs run simultaneously they will DEADLOCK.
I'll try to make a time diagram, you may have to view it in a non-proportional font.
1. Program-1 | Program-2
2. Program line | Program line
3. Program line | Program line
4. do transaction: | Program line
5. find first TABLE-A exclusive lock where ....... | do transaction:
6. Program line | find first TABLE-B exclusive-lock ...
7. Program line | Program line
8. find first TABLE-B exclusive lock where ....... | Program line
9. Some more data processing | find first TABLE-A exclusive lock ....
10. end. /* Transaction */ | Some more data processing
11. | end. /* Transaction */
In step 8 Program-1 can't lock TABLE-B (It is locked by Program-2 in step 6) and is therefore halted by Progress.
In step 9 Program-2 can't lock TABLE-A because Program-1 locked it instep 5.
The two programs are now deadlocked, and will wait forever.
To monitor locks use the promon utility "Record locking table".
Hope this clears things up for you.
Regards
Mike
