How does the db library work??

How does the db library work?? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How does the db library work?? (/showthread.php?tid=32219)

How does the db library work?? - El Forum - 07-16-2010

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

!

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.

How does the db library work?? - El Forum - 07-16-2010

[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";

How does the db library work?? - El Forum - 07-16-2010

[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.

How does the db library work?? - El Forum - 07-16-2010

[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.

How does the db library work?? - El Forum - 07-17-2010

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

How does the db library work?? - El Forum - 07-18-2010

[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.