如何在mysql中获取更新行的主键？

In a database with the two tables users and apples, I want to assign an apple to a user, and know the apple id afterwards. Multiple apples can be assigned to one user. I work in PHP.

// users asks for an apple - lets find a vacant one
$apple_id = $dbh->query("SELECT id FROM apple WHERE user_id IS NULL LIMIT 1")->fetchColumn();

// hope fully we found an apple, lets assign it
$sth = $dbh->prepare("UPDATE apple SET user_id = ? WHERE apple_id = ?");
$sth->execute(array($user_id,apple_id));

The problem is that another transaction could choose the same apple, and then one user would override the apple from the other. If I include AND user_id IS NULL in the update statement, I run the risk of not getting an apple.

I know that I could just go with UPDATE apple SET user_id = ? WHERE user_id IS NULL, but then I would not know the apple id afterwards.

Is there a way to get the primary key of an updated row? Something like lastUpdatedId would be nice.

But I think I have to use a transaction. But which Isolation level do I have to choose? Repeatable read? Serializeable? And why? And what are the implications of this? Will it lock only the row or the whole table?

You can do something like the following:

update apple
    set user_id = if(@apple_id := apple_id, ?, ?)
    where user_id is null
    limit 1;

select @apple_id;

SELECT ... FOR UPDATE will produce exactly the desired effect:

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

$dbh->beginTransaction();

// users asks for an apple - lets find a vacant one
$apple_id = $dbh->query("SELECT id FROM apple WHERE user_id IS NULL LIMIT 1 FOR UPDATE")->fetchColumn();

// hope fully we found an apple, lets assign it
$sth = $dbh->prepare("UPDATE apple SET user_id = ? WHERE apple_id = ?");
$sth->execute(array($user_id,apple_id));

$dbh->commit();

The second transaction will wait until the first has commited.