How does the db library work??

[eluser]minerbog[/eluser]
My title is a bit strange but hopefully it will become clear soon !

When using the DB library in CI does the $this->db->query command lock anything??

The reason why I ask is as follows:

I have the following code

Code:

$sql = "SELECT * FROM account WHERE id = '1'";
$this->db->query($sql);
$row = $query->result();

$total = $row['amount'] + $newamount;

$sql = "UPDATE account SET amount='".$total."' WHERE id='1' LIMIT 1";
$this->db->query($sql);

I know it very very unlikely, but could it be possible for user1 to read the amount and user2 the read the old amount before user1 has updated the record?

I'm not sure id it a CI issue or a MYSQL issue but I know you guys are really clever and will know the answer!!

Thanks

Gavin.

[eluser]danmontgomery[/eluser]
The time between the select and update is a matter of microseconds. Possible? Sure. Likely? No. You could also eliminate that possibility by putting the whole statement in one query.

Code:

$sql = "UPDATE account SET amount = amount + ".$newamount." WHERE id = 1 LIMIT 1";

[eluser]minerbog[/eluser]
[quote author="noctrum" date="1279305527"]You could also eliminate that possibility by putting the whole statement in one query.

Code:

$sql = "UPDATE account SET amount = amount + ".$newamount." WHERE id = 1 LIMIT 1";

[/quote]

Thanks noctrum, how would I implement your answer as I still have to get the amount from the db in the first place? Thus doing more than one db call.

[eluser]mddd[/eluser]
@minerbog:
noctrum's example does both in one go. It tells Mysql to take the current value of amount, and add $newamount to it. You don't need to get the amount first. It can be done in one call, no problem.

[eluser]minerbog[/eluser]
Thanks guys great help

[eluser]KingSkippus[/eluser]
FYI, if you run into a more complicated case where you have to ensure that your database is always in a consistent state even across different threads, I highly advise you to read up on transactions. Everything enclosed within a transaction is an all-or-nothing prospect. If you're dealing with financial databases or other business critical systems, this is most certainly the way to go.