• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Locking Whole Database

 
Lora Louise
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It seems a lot of people lock the entire database by using a special variable that is in addition to and separate from record locks. I wonder if there's a problem with that logic and if the entire database should instead be locked record by record. I assume that if the user locked the entire database, they should have modification access to all records.
Imagine this scenario, calls from the client:
User A User B
Lock record 1 OK
Lock whole database OK
Get num of seats
Get num of seats
Decrease record 1 num of seats
Decrease rec 1 num seats
Unlock database
Unlock record 1
It seems User A's information about the number of seats could be corrupted by User B's actions.
 
Lora Louise
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The format of the scenario was lost. Here's the logic:
User A - Lock record 1 OK
User B - Lock whole database OK
User A - Get num of seats
User B - Get num of seats
User B - Decrease record 1 num of seats
User A - Decrease rec 1 num seats
User B - Unlock database
User A - Unlock record 1
It seems user A could end up booking more seats than there really are because the number of seats is updated without A's knowlege by the whole database owner.
 
Raju, Gentle
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am a little confused with the order of User A and B's responsibilities in the given case. If user B locks the
database why would it continue booking seats ? By the way
user B does not have any information about which record
it is suppose to get and reduce seats for booking. In the
case of User A it knows it is suppose to reduce seats for
the record number 1.
I think if the approach is to by using a special variable that is in addition to and separate from record locks then this special variable needs to be checked while locking database or any records. So I am a little confused with the order that I am seeing here. Please let me know if I am missing anything here or anything that I have not considered.

[This message has been edited by Raju, Gentle (edited September 24, 2001).]
 
vladimir levin
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree with Lora that locking the entire database is
logically equivalent to locking each record. The
corollary of such logic is that one cannot lock the entire
database while any record-level locks exist.
 
Lora Louise
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Raju,
Suppose the FBN administrator had to go in and reduce the number of seats on all flights by 10 for whatever reason. Maybe they were renovating those seats or something. The administrator would probably just lock the whole database.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by vladimir levin:
I agree with Lora that locking the entire database is
logically equivalent to locking each record.
Not entirely - while the database is locked, it should arguably not be possible to add a new record. This does not affect your corollary of course.
- Peter
 
Gennady Shapiro
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just consulted my database guys and we seem to agree with Laura and Vladimir. If you lock a record (or rather a page in DB) no one else can over-lock it. Consiquently, user B's action of locking database after user A has locked a record should fail entirely.
Of course, different DBMS do it differently, moreover, our DB is not a traditional sql DB. But this general principal should still apply.
 
vladimir levin
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Peter, good point! You're absolutely right. Therefore the
only reasonable solution is to have a separate flag to mark
the database as being locked.
Originally posted by Peter den Haan:
Originally posted by vladimir levin:
[b]I agree with Lora that locking the entire database is
logically equivalent to locking each record.
Not entirely - while the database is locked, it should arguably not be possible to add a new record. This does not affect your corollary of course.
- Peter
[/B]

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic