CodeIgniter Forums
single transaction across a number of functions/stored procedures - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: single transaction across a number of functions/stored procedures (/showthread.php?tid=1264)

single transaction across a number of functions/stored procedures - obiron - 02-24-2015

Guys,  I am about to embark on a new application using AngularJS, CodeIgniter 3.x and MySQL (InnoDB) using the  PDO extensions.

The design protocol I am going for is that user validation will be done client-side in Angular and complex business rules will be handled by the database with all reads coming via views and all Create/Update/Delete commands being handled by stored procedures.  CodeIgniter will act as the API layer, intercepting the angular REST requests, translating the JSON into something that MySQL can handle and managing the ACID transactions, and error handling.


In Angular land you send data back to the API as a JSON object.  Sometimes my JSON will be a complex object in that is will contain an independant object (the Customer) a second object (order) which will have one header object and two collections of objects (order lines and payment lines)

Each of these objects will update a different table in the database and so I need an atomic transaction to commit or rollback the entire database update.

If I use
Code:
&CI->db->begin_tran()...&CI->db->end_tran()
. in an outer function, then iterate through each JSON object and call a separate function in the model for each insert/update: each insert/update will be done by preparing arguments and then calling a stored procedure.

First Question:
Will CI and MySQL honour the transactional scope across a number of db calls, especially when those calls come from separate CI functions and MySQL sprocs

Second Question:
If I have multiple users and they try to create/update different orders at the same time, how does CI/MySQL understand which database requests belong to which transaction scope, seeing as they are all being called with the same user.  

Thanks in advance

Obiron