-4

I have a query, i have written a program in such a way an entry created in database and i am getting last_id, used this last id to update in second table.

$table='user';
$data = array('array having user data');
$this->db->insert($table, $data);
$last_id = $this->db->insert_id();

$this->db->where('user_id', $last_id);
$this->db->set('wallet_total_amount', "wallet_total_amount");

In such program when two entries created at exactly same time then the id of second transaction updated with first record. how to avoid this. please suggest.

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
codex
  • 446
  • 1
  • 7
  • 17
  • Can you show us the 1064 error – RiggsFolly Aug 21 '18 at 12:22
  • 2
    You should learn how to use transactions, have a look to this answer: https://stackoverflow.com/a/2708247/1911019 – Alberto Aug 21 '18 at 12:23
  • 1
    "In such program when two entries created at exactly same time" How?? you only INSERT once.. Or do you want to ask if this approach is prone to **race condition** ? – Raymond Nijland Aug 21 '18 at 12:23
  • 1
    I am not totally clear what you are asking. looks like you are getting the new id created in the first INSERT and using it to do _something_ but what you are not showing us to another table – RiggsFolly Aug 21 '18 at 12:25
  • 3
    MySQL's `last_insert_id` is tied to the connection. While a transaction is definitely a good idea, it's not necessarily required for this. Rolling back in case of an issue though, absolutely – Phil Aug 21 '18 at 12:31
  • 3
    _“In such program when two entries created at exactly same time then the id of second transaction updated with first record”_ - no, it is not, because the last insert id is _connection_-specific. – CBroe Aug 21 '18 at 12:33

1 Answers1

-2

You need to use transactions, basically committing one query after the other. Transactions allow you to perform one set of queries at a time, locking the database until all the queries are run (or rolled back, in case of errors).

User 1

auto commit off

  1. Run query 1 (Insert data)
  2. Retrieve last_insert_id
  3. Run query 2 using last_insert_id

commit

User 2

auto commit off

  1. Run query 1 (Insert data)
  2. Retrieve last_insert_id
  3. Run query 2 using last_insert_id

commit

To use transactions in codeigniter, have a look to this answer. Here you can find the PHP documentation.

Sparky
  • 98,165
  • 25
  • 199
  • 285
Alberto
  • 674
  • 13
  • 25
  • Note he is using a framework atleast link to the frameworks docs how to do transactions there. – Raymond Nijland Aug 21 '18 at 12:31
  • He wasn't asking that directly (that's why I've only tried to explain the theory), by the way thank you for the suggestion, I'm editing the answer. – Alberto Aug 21 '18 at 12:34
  • You do not need to use a transaction to do what I believe is being asked – RiggsFolly Aug 21 '18 at 12:38
  • 1
    `last_insert_id` is related to the connection. 2 scripts running at the same time would have different connections and therefore `last_insert_id` would not be confused between 2 scripts – RiggsFolly Aug 21 '18 at 12:40
  • I don't have his code used to connect to the database, what I understood from his question is that he's trying to insert data, get the last id resulted from the insert query, then insert some other data using the last_id. He's saying that if an other user is doing [whatever action], the last insert id used is the incorrect one. This issue should be fixed using transactions, preventing the db to operate on the same time for different users. – Alberto Aug 21 '18 at 12:43
  • @RiggsFolly, I understand and I know that the last_insert_id is related to the connection, but if that guy has that issue, a possible solution (to cut out whatever way he's using to connect to the database) would be using transactions. I know people around use singleton to handle database connections, in that case everytime a new query is done, the same connection is used. – Alberto Aug 21 '18 at 12:51
  • I dont think he has this issue, he just wants to know if an issue could happen – RiggsFolly Aug 21 '18 at 12:56
  • I think that with "In such program when two entries created at exactly same time then the id of second transaction updated with first record. **how to avoid this.** please suggest." he's actually asking why he's having this issue and how to solve it. – Alberto Aug 21 '18 at 12:58
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/178451/discussion-between-alberto-and-riggsfolly). – Alberto Aug 21 '18 at 13:04