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