CODEWITHSUNDEEP
mysqltransactions
robinmag
robinmag

Reputation: 18120

mysql REPEATABLE_READ and not yet existed row

In MySQL with REPEATABLE_READ isolation mod, my transaction is like this: select a row by pk,if it doesn't exist then insert it:

SELECT * FROM users where user_id = 10; INSERT INTO users(user_id) VALUES (10);

My question is if this row doesn't exist, how can MySQL enforce the row locking to prevent another transaction to insert this row ?

Upvotes: 1

Views: 231

Answers (2)

Jon Black
Jon Black

Reputation: 16569

why are you even bothering attempting re-using user_ids (filling gaps) - do you think you'll run out of key values ??

create table users
(
user_id int unsigned not null auto_increment primary key,
...
)
engine=innodb;

int unsigned has a possible range of 0 to 4294,967,295 (4 billion) or to put it another way... 8 times as many users as there currently are on facebook or half the entire population of the planet.

if you think your app is gonna go that viral you could use a bigint unsigned which is large enough to store the number of seconds that have elapsed since the beginning of time (big bang)

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30141

Depending on what you actually need to do with the record, here are a few options:

  1. If you only need to perform a "If exists then update, otherwise insert" the best option would be to use INSERT users(user_id, visits) VALUES (10,1) ON DUPLICATE KEY UPDATE visits = visits + 1, since this operation is atomic.

  2. Try to insert a new row using INSERT IGNORE, and check the ROW_COUNT() function to find out if the row was inserted, this is better then option #3 since it avoids deadlocks.

  3. Add FOR UPDATE to your select statement, and rely on InnoDB's next key locks.

how can MySQL enforce the row locking to prevent another transaction to insert this row?

InnoDB uses "gap" locks, which log the empty gap in the index.

In your example, if there are users 8 and 12, InnoDB will lock the space between 8 and 12, preventing other transactions from inserting user_id 10.

From the docs: http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table

Also read http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

Upvotes: 3

Related Questions