Re: What am i up against
In article [EMAIL PROTECTED], Stuart Felenstein [EMAIL PROTECTED] writes: How am I keeping transactions open ? Since I don't want to do a transaction till the very end. All I'm doing is bringing the data to last stage. After it's all been collected. You don't keep transactions open. You collect all the necessary data by PHP, and when you've got all of them (i.e. on the last page) you do BEGIN; INSERT INTO table1 (data1); INSERT INTO table2 (data2); INSERT INTO table3 (data3); COMMIT; all in one go. Note that all tables must be InnoDB for that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What am i up against
This maybe somewhat of a silly question. Scenario - I am pretty much a noob at both relational databases and web programming. I've built most of my pages using a RAD tool that, for the most part, does a decent job , if you keep it fairly simple. One of the limitations is inserts updates are done on a one form on one page into one table basis. There is a MtM feature. Anyway, now I'm flying solo and have created a form that spans 5 pages and will insert into (I lost count) I believe 3-5 tables. I want to make sure I make provisions for rollback. All but one table is innodb. While Im reading and digging around, wondering is this a massive insert statement ? Would joins need to be involved ? I'm imagining it's more of a step by step (1 table at a time) process. With rollback, if an insertion is already done into 1 table , and the insert into table 2 fails, does that mean table 1's insertion would be deleted ? I think that is probably enough and I apologize for asking what are basic questions and a bit scattered at that. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What am i up against
If you are saying the user would navigate through multiple pages updating a table on each page and you want to treat all the updates collectively as one transaction, that is a bad idea. You want to pass all the data required for a single transaction in one request so it can be committed or rolled back as part of the same request. Web pages are generally stateless unless you are using session state variables which is not a good idea in terms of scalability. You don't want to keep transactions open from page to page. If you have a transaction that updates multiple tables and you roll it back, it will negate all changes (inserts, updates, deletes) that occurred within the transaction. Hope that helps, not sure I'm understanding your question. Regards, Joe Audette Stuart Felenstein [EMAIL PROTECTED] wrote: This maybe somewhat of a silly question. Scenario - I am pretty much a noob at both relational databases and web programming. I've built most of my pages using a RAD tool that, for the most part, does a decent job , if you keep it fairly simple. One of the limitations is inserts updates are done on a one form on one page into one table basis. There is a MtM feature. Anyway, now I'm flying solo and have created a form that spans 5 pages and will insert into (I lost count) I believe 3-5 tables. I want to make sure I make provisions for rollback. All but one table is innodb. While Im reading and digging around, wondering is this a massive insert statement ? Would joins need to be involved ? I'm imagining it's more of a step by step (1 table at a time) process. With rollback, if an insertion is already done into 1 table , and the insert into table 2 fails, does that mean table 1's insertion would be deleted ? I think that is probably enough and I apologize for asking what are basic questions and a bit scattered at that. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.joeaudette.com http://www.mojoportal.com
Re: What am i up against
See interspersed: --- Joe Audette [EMAIL PROTECTED] wrote: If you are saying the user would navigate through multiple pages updating a table on each page and you want to treat all the updates collectively as one transaction, that is a bad idea. I agree, that is what I am trying to not do. You want to pass all the data required for a single transaction in one request so it can be committed or rolled back as part of the same request. Exactly what I want to accomplish. Web pages are generally stateless unless you are using session state variables which is not a good idea in terms of scalability. You don't want to keep transactions open from page to page. I don't understand this. I assume you are referring to the application session variables (and I'm using PHP). Yes I'm using session variables to collect the data. How am I keeping transactions open ? Since I don't want to do a transaction till the very end. All I'm doing is bringing the data to last stage. After it's all been collected. If you have a transaction that updates multiple tables and you roll it back, it will negate all changes (inserts, updates, deletes) that occurred within the transaction. Hope that helps, not sure I'm understanding your question. That's because my question was somewhat convoluted due to me not completely understanding all of it myself. Stuart Regards, Joe Audette -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]