CODEWITHSUNDEEP
mysqlinnodb
rockstardev
rockstardev

Reputation: 13537

MySQL: do you explicitly have to say which row must be locked or does INNODB just know it?

I know INNODB has row-level locking. So if I do this:

LOCK TABLES users WRITE
SELECT credits FROM users WHERE userid = 1;
UPDATE users SET credits = ([CREDITS] + 100) WHERE userid = 1;
UNLOCK TABLES

Is innoDB smart enough to lock only that row for that second, or is there some other step I must take in order to lock only the row? How does innodb know only to lock this one user during my duration? Is it locked at the SELECT already as it should be?

Upvotes: 0

Views: 135

Answers (1)

Zelldon
Zelldon

Reputation: 5516

You have no need to lock the table on your own if you create the table with the option Engine = InnoDB a InnoDB monitor will be created.

CREATE TABLE t (i INT) ENGINE = InnoDB;

The monitor locks the table automatically, but the lock is not for a single row InnoDB will lock the hole table for the operation/transaction.

Maybe the user want only to Read the Table he gets the s lock on the hole table. Simultaneously a other user can also read the table, but if he wants to delete a row and he requests the x lock he must wait of the other user, until he released his s lock.

MYSQL lock type compatibility matrix.

    X           IX            S          IS
X   Conflict    Conflict    Conflict    Conflict
IX  Conflict    Compatible  Conflict    Compatible
S   Conflict    Conflict    Compatible  Compatible
IS  Conflict    Compatible  Compatible  Compatible

Please read the mysql doc.:

InnoDB monitor - http://dev.mysql.com/doc/refman/5.1/en/innodb-monitors.html

InnoDB lock modes - http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html

Upvotes: 1

Related Questions