SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question Lock Rows in INNODB

    Hello


    I created a table innodb

    Code:
    CREATE TABLE `innodb` (
      `id` int(5) NOT NULL auto_increment,
      `name` varchar(9) collate latin1_general_ci NOT NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=21 ;

    and inserted some data into it


    Code:
    INSERT INTO `innodb` (`id`, `name`) VALUES 
    (4, 'bean'),
    (16, 'beep'),
    (8, 'bindas'),
    (14, 'done'),
    (20, 'harry'),
    (12, 'heal'),
    (7, 'indus'),
    (3, 'jim'),
    (18, 'laula'),
    (15, 'lip'),
    (13, 'none'),
    (5, 'olive'),
    (10, 'paul'),
    (17, 'paula'),
    (9, 'popla'),
    (6, 'raphel'),
    (2, 'saurab'),
    (1, 'hem'),
    (19, 'tom'),
    (11, 'zeal');

    and when I ran a query to lock the table


    Code:
    SELECT *
    FROM `innodb`
    LOCK IN SHARE MODE
    LIMIT 0 , 30


    I got the following error:


    Code:
    Error
    
    SQL query: Documentation
    
    SELECT *
    FROM `innodb`
    LOCK IN SHARE MODE
    LIMIT 0 , 30
    
    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 2


    I was wondering what wrong in my query.

    Please assist.


    Many Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Move the 'LOCK' statement to the last line of the query

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ok, but even the following code gives error

    Code:
    SELECT *
    FROM `innodb`
    LOCK IN SHARE MODE

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What error?

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Error

    SQL query: Documentation

    SELECT *
    FROM `innodb`
    LOCK IN SHARE MODE


    MySQL said: Documentation
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 2

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    But if you don't have a LIMIT in your query!
    Are you sure you're testing this query

    SELECT *
    FROM `innodb`
    LOCK IN SHARE MODE

    ?

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    But if you don't have a LIMIT in your query!
    Are you sure you're testing this query

    SELECT *
    FROM `innodb`
    LOCK IN SHARE MODE

    ?
    hey it worked when I ran it under SQLYog.

    ok, I now have a question, how do we test if the locking is actually working?

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Open another window and try to update something?

  9. #9
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    it allowed me to update record

    Code:
    UPDATE innodb 
    	SET
    	NAME = 'name'
    	
    	WHERE
    	id = 16 ;
    returned:

    Code:
    (1 row(s) affected)
    Execution Time : 00:00:00:000
    Transfer Time  : 00:00:00:000
    Total Time     : 00:00:00:000


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
  •