SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    question about lockin tables

    Hi

    I have a table in which texts will be stored. Here is its structure:
    Code:
    CREATE TABLE `userstexts` (
      `textID` mediumint(8) unsigned NOT NULL auto_increment,
      `textTitle` mediumtext NOT NULL,
      `textText` varchar(255) NOT NULL default '',
      `category` smallint(5) unsigned NOT NULL default '0',
      `postedDate` int(10) unsigned NOT NULL default '0',
      `readNumber` mediumint(8) unsigned NOT NULL default '0',
      `newAdmin` tinyint(3) unsigned NOT NULL default '0',
      `userID` mediumint(8) unsigned NOT NULL default '0',
      PRIMARY KEY  (`textID`),
      KEY `text` (`textText`)
    ) TYPE=MyISAM AUTO_INCREMENT=13 ;
    As you can see, there is readNumber col. This col will +1 updated each time a specific text will be viewed by another user. I will LOCK this table to perform the Update query. However I was wondering if doing so would not be a problem for SELECT statements. Will they still work without any problems even if the table is locked? Should I create a specific table to store the number in question (-->'how many times a text has been viewed').

    I hope I was clear (but I'm not sure)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why do you think you need a lock?

    just issue the UPDATE

    individual statements are atomic

    unless i'm, like, totally out to lunch on this one...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudy, once again thank you for taking care of my problem

    Shame, I realized that I could use a statement like this one instead of SELECTING the value to add 1 to it:
    Code:
    UPDATE userstexts 
    SET readNumber=readNumber+1 
    WHERE...
    My concerns were that if someone else viewed the same text while the new value was being calculated, the increment might have been problematic.

    hum, does it make sense?


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    as far as i understand the way databases work, a statement like the one you just showed cannot possibly have a problem

    i mean, there's no way

    any other statement that wants the same row before your update, they get the unincremented value

    during your update, no other statement is allowed to see the row, and you do not have to "lock" this to make it happen, this is the way it happens by default -- row operations are atomic, or something like that

    after your update, all other statements see the incremented value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure thing with the readNumber=readNumber+1 trick. But if, like I thought in the first place, I would have to select the value, add 1 to it, and then perform the update query, a problem might have occured. Isn't it?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    oh, i see what you meant! yes, you are absolutely right, anybody who does

    1. select a row
    2. add 1 to value
    3. update row with new value

    is really asking for trouble!!!

    this is because there are actually two database statements, which would need to be locked together as a transaction in order to work safely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    transaction.

    the word I neeed. Thanks rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •