I have a question regarding transaction handling through PHP. Please note I have checked the following questions but wasn't satisfied exactly.
Get the new record primary key ID from mysql insert query?
MySQL get Primary Key after Insert Statment
In the first question Duncan explains that there will be concurrency issue and needs to solve it through PHP. But I have no clue how to do that.
Let me first give you the database structure.
create table T1 (
id int NOT NULL AUTO_INCREMENT,
name varchar(10),
PRIMARY KEY (id)
)
create table T2 (
id int NOT NULL AUTO_INCREMENT,
T1_id int not null,
name varchar(10),
PRIMARY KEY (id),
FOREIGN KEY (T1_id) references T1(id)
)
create table T3 (
id int NOT NULL AUTO_INCREMENT,
T1_id int not null,
name varchar(10),
PRIMARY KEY (id),
FOREIGN KEY (T1_id) references T1(id)
)
Now I have to insert data to these in a single shot through my PHP Webservice. So in the PHP code, I am executing these four queries in a row -
(Q1) insert into T1 (name) values ('ABC');
(Q2) pk = SELECT LAST_INSERT_ID();//pseudocode
(Q3) insert into T2 (T1_id, name) values (pk, 'XYZ');
(Q4) insert into T3 (T1_id, name) values (pk, 'MNP');
At the start of execution I am setting auto-commit as false and then depending on success or failure executing commit/rollback.
But won't there be any concurrency issue? Let's say T1 has got two rows before we start the concurrent test. So when Q2 gets executed for user U1, he/she will 3 whereas user U2 will also get 3 because no commit has been made. If that's the case how to solve it?
