#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171

    Why does a secelct lock the whole database?


    Hi;

    On localhost, running a basic SELECT on a relatively big table (+6 million records) locks everything. I cannot access any other PhpMyadmin page unless the query is executed successfully!

    I understand the column needs to be indexed and it will work much faster but that is not the point here, the point is why does it lock the whole phpmyadmin? Thanks
    Code:
    CREATE TABLE `products_table` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `code` varchar(4) NOT NULL,
     `class` varchar(11) NOT NULL,
     `category` varchar(5) NOT NULL,
     `price` decimal(6,2) NOT NULL,
     `production_date` date NOT NULL,
     `description` text NOT NULL,
     PRIMARY KEY (`id`),
     KEY `production_date` (`production_date`),
     KEY `index_price` (`price`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6162062 DEFAULT CHARSET=latin1
    Code:
    SELECT id
    FROM   `products_table`
    ORDER  BY description DESC
    Last edited by zxcvbnm; May 1st, 2013 at 08:25 PM.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Opening a session in PHP locks the session file. Subsequent HTTP requests to PHP scripts will block on session_start until the first HTTP request closes the session file. PHPMyAdmin holds the session file open while executing a query.

    MySQL does not lock the entire database when running a select.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by E-Oreo
    Opening a session in PHP locks the session file. Subsequent HTTP requests to PHP scripts will block on session_start until the first HTTP request closes the session file. PHPMyAdmin holds the session file open while executing a query.

    MySQL does not lock the entire database when running a select.
    I see! I can still access phpmyadmin from another browser.
    Hey what source do you recommend me to read about HTTP? I dont know much about it. Thanks
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    I would start with Wikipedia.

    This problem doesn't actually have much to do with HTTP though, it's due to the way PHP handles sessions.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by E-Oreo
    I
    You seem to be phpmyadmin guy

    Just wondering, if I write a query ( that takes 1 hour to excute ) in SQL box in phpmyadmin and click Go, the browser starts loading and the query starts to be executed. If I leave it that way, after an hour it says the query has been executed successfully.

    Now my question is what happenes if I close the browser after it has already started loading? My guess is that if will continue the script and will successfully finish it.
    Am I right?

    Thanks
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    Why don't you close it and see what happens.
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by TonyF123
    Why don't you close it and see what happens.
    The script keeps going and I am not able to access phpmyadmin from same browser until it does it's job. I check here because everything E-Oreo says is a useful lesson.

IMN logo majestic logo threadwatch logo seochat tools logo