1

First of all, I want to INSERT data in two TABLE at a time. I know it is not possible. Due to this limitation, i foresee a problem that could occur if two REQUESTS(to insert data) simultaneously occur.

Is there any way that when i INSERT data in first table. No one could INSERT data in the first TABLE as i am entering a relational data to avoid wrong linking of data (which could occur because i am using the mentioned function to get the Last inserted ID) mysqli_insert_id();.

                  UPDATE

EXAMPLE:            ILLUSTRATION (PSEUDO CODE)
            (example is added as my problem was not clearly addressed)

REQUEST-1:

//ClientRequest-1
1. INSERT data in table-A
2. GET INSERT-ID
3. INSERT data in table-B

REQUEST-2:

//ClientRequest-2
1. INSERT data in table-A
2. GET INSERT-ID
3. INSERT data in table-B

CHALLENGE:

What if the both the requests get processed parallely without an error (Pre-Requisite) in the following order(multi threaded request):

01. Point # 1 of Request-1

02. Point # 1 of Request-2
03. Point # 2 of Request-2
04. Point # 3 of Request-2

05. Point # 2 of Request-1
06. Point # 3 of Request-1

The problem is:

When Points 02,03,04 get executed, will the insert_id get affected for Request-1

Is it the right way or any other way to do it?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Asif Mehmood
  • 964
  • 2
  • 14
  • 35
  • 1
    You're looking for a transaction. – Mjh Nov 09 '15 at 10:18
  • I am new, yes may be like that. @Mjh – Asif Mehmood Nov 09 '15 at 10:18
  • 1
    I'd explain further, but just googling for a "MySQL transaction" will yield great results. A transaction means that either ALL queries are executed (within the transaction) or none. That lets you stop worrying about "what happens if requests occur simultaneously". Using a transaction, you're isolating a set of queries (be it insert, delete or update) from the other queries that are occurring. In your case, that would mean you can simply insert the data and not worry. – Mjh Nov 09 '15 at 10:30
  • Yes, i was looking for it. Thanks. I'll get to the point sooner thanks very much – Asif Mehmood Nov 09 '15 at 10:32
  • @Mjh kindly look the UPDATE in question. This was my problem. Thanks for the support – Asif Mehmood Nov 09 '15 at 12:16
  • 1
    Transactions solve the problem you depicted :) – Mjh Nov 09 '15 at 13:21
  • And please confirm one more thing, is it right that the first_requested_data to be inserted into table-B will not be linked to second_requested_data of table-A (in case of parallelism) @Mjh (it is most precise of what i already asked) – Asif Mehmood Nov 09 '15 at 13:23
  • 1
    Exactly, your first request will be completely isolated from any other request, if it's in a transaction. – Mjh Nov 09 '15 at 13:25
  • Hurray, at last i understood :-D thanx mate very much, it was a cool time lolx @Mjh – Asif Mehmood Nov 09 '15 at 13:29

1 Answers1

2

You are getting it bit confused. All DML operation (insert, update, delete) holds a implicit row level or table level lock on the table and so even if the processing happens in multitreaded way another DML won't succeeded unless the lock gets released by previous DML operation (in your case it's INSERT).

For better reliability you can consider placing your DML code block inside a TRANSACTION block explicitly to make sure atomicity of the operation.

Rahul
  • 76,197
  • 13
  • 71
  • 125