I am most likely overcomplicating this. I am fairly confident with MySQL but never used transactions before. I know the concept is begin(), do stuff, commit() or rollback() on a failure and I am pretty sure I can structure that with ease.
What I want to find out is during a transaction I want to update a table then use that updated value in another query during the same transaction. Heres an outline:
begin()
INSERT
SELECT FROM INSERT
UPDATE BASED ON SELECT
commit()
Obviously I have slimmed down the code here and this on its own means nothing. I would like to know if this concept works before I go too deep into transactions and find it doesn't work.
My actual transaction is going to be about 5 times larger and parts of it rely on other parts of the unfinished transaction as above.
I am using Laravel so my code uses DB::beginTransaction() DB::commit() and DB::rollback() if this makes any difference to the question.
So, the simple answer to this is yes.
I ran through a number of tests with the code I had been developing and this is the state, at least in my case.
DB::beingTransaction()
Now we do our thing. ALL STATEMENTS HERE ARE RELATIVE TO EACH OTHER AND PERFORMED IN ORDER.
DB::commit()
This then runs all the statements in order as long as the simulated run through was a success
DB::rollback()
This is called on ALL methods where changes could potentially fail within a try catch block
So let me provide a working (simplified) Laravel example to demonstrate the basics:
public function store(Request $request)
{
DB::beginTransaction();
try
{
if(!$this->setAuthorisation($request))
throw new Exception('Failed to set authorisation');
DB::commit();
return response()->json(['status' => 'OK'], 200);
}
catch (Exception $exception)
{
DB::rollBack();
return response()->json(['status' => 'Failed', 'error'=>$exception->getMessage()], 500);
}
}
Now from here we can access the changes made in the setAuthorisation() method say in a getAuthorisation() method and then apply the data from $data = getAuthorisation() to another set of code say for example in setData($data).
The entire transaction works in order of simulated events before commiting so it "applies" the changes before "actually applying" the changes. Its really hard to explain any more than that so I hope this answers my own question.