Lock Rows in INNODB

Databases
1

Hello

I created a table innodb


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


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

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

I got the following error:

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

Move the ‘LOCK’ statement to the last line of the query

3

ok, but even the following code gives error

SELECT *
FROM `innodb`
LOCK IN SHARE MODE
4

What error?

5

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

Open another window and try to update something?

7

But if you don’t have a LIMIT in your query! :goof:
Are you sure you’re testing this query

SELECT *
FROM innodb
LOCK IN SHARE MODE

?

8

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?

9

it allowed me to update record :frowning:

UPDATE innodb 
	SET
	NAME = 'name'
	
	WHERE
	id = 16 ;

returned:

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