Re: Do I need Innodb?
Hi Ron, My Scenario is if two users open an existing record with data already in the fields. They then add some information to the end of a field with data. Now when they click update the last user/browser gets written to the db, where the first users data is over written. I thought innodb would help this.. user a: user b: pulls up record 5 pulls up record 5 adds/appends line to field badds/appends line to field b clicks update first clicks update second user b's line is added/appended to record 5, user a's line is overwritten not even seen. Ron Is this what you mean? $data = old; A: SELECT data FROM table WHERE id=5; ## get old; B: SELECT data FROM table WHERE id=5; ## get old; A: $data .= updated_by_A; B: $data .= updated_by_B; A: UPDATE table SET data='$data' WHERE id=5; ## data is old updated_by_A now B: UPDATE table SET data='$data' WHERE id=5; ## data is old updated_by_B now But you want the last data to be old updated_by_A updated_by_B. If that is what you mean, then I think you can't solve it with transaction. $data = old; A: START TRANSACTION; A: SELECT data FROM table WHERE id=5; ## get old B: START TRANSACTION; B: SELECT data FROM table WHERE id=5; ## still get old A: $data .= updated_by_A; B: $data .= updated_by_B; A: UPDATE table SET data='$data' WHERE id=5; B: UPDATE table SET data='$data' WHERE id=5; ## waiting for A to commit A: COMMIT; ## data is old updated_by_A now B: COMMIT; ## data is old updated_by_B now (same as above) Maybe you should use LOCK TABLE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do I need Innodb?
* Ron McKeever I thought update is for existing records and insert is to add new records? That is correct. My Scenario is if two users open an existing record with data already in the fields. They then add some information to the end of a field with data. They add information, you say? As in add new records...? No, they don't add new records, and that may be your problem/confusion. Now when they click update the last user/browser gets written to the db, where the first users data is over written. You say they click update, I assume that means they click a button labeled update, and this makes your program/script execute an UPDATE statement against the database? Not an INSERT statement? I thought innodb would help this.. No, it won't. user a: user b: pulls up record 5 pulls up record 5 adds/appends line to field badds/appends line to field b clicks update first clicks update second user b's line is added/appended to record 5, user a's line is overwritten not even seen. Yes, what would you expect? You could have seen user a's line if you checked before user b pushed 'update', right? The problem is that the user b update does not know that user a did an UPDATE _between_ the SELECT statement and the UPDATE statement of user b. As a result the update from user a is overwritten, as you have observed. You describe the update as adds/appends line to a field. It's still an update to a single field, as seen from the database. Take a look at your program/script, the relevant SQL probably says something like UPDATE ... SET field_b=$userinput, ... where $userinput comes from the multiline field. No append here... just assigning the user input to the field_b column. Now what you _could_ have is something like UPDATE ... SET field_b=CONCAT(field_b,$userinput), ... This would _append_ the user input to the existing content of the column, but it will probably not give you the result you are looking for. It may work if the user input is only the new lines, without the old. There are many possible ways around this problem: 1. Ignore it. Depending on the application, this may happen very seldom (or very often!), you may be in a position where you can ignore the few cases where user input is lost. 2. Avoid the problem by notifying a user that the record is beeing updated by a different user, preventing two users to edit the same record at the same time. May or may not be applicable in your case. 3. Use a checksum or a hidden copy of the original data and re-read the record to verify that the record is not changed while it was edited by the user. If it has, you can either reject the last change with a message, or if you feel brave, merge the changes of user b into the allready saved changes of user a. If the change is allways 'adding lines' this may be easy. 4. Normalize. Put the multi-line field in a separate table, for instance called 'textlines', with one line (varchar, hopefully there are no lines more than 255 characters...) for each row. Multiple users can append lines at the same time. In general, I would prefer the first or the last solution. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do I need Innodb?
On Fri, 16 Jan 2004 13:43:55 -0800 Ron McKeever [EMAIL PROTECTED] wrote: Scenario 1: I have noticed that if two users open a record to edit it in two different browsers and they edit the same field and then click update, the last user/browser gets written to the db, where the first users data is over written. I would hope it would write user ones info then write user twos info, without over writing user ones updates Will INNODB table type fix Scenario 1? No. You should use INSERT instead of UPDATE if you don't want the data to be overwritten. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do I need Innodb?
hi, Thanks for the response. I thought update is for existing records and insert is to add new records? My Scenario is if two users open an existing record with data already in the fields. They then add some information to the end of a field with data. Now when they click update the last user/browser gets written to the db, where the first users data is over written. I thought innodb would help this.. user a: user b: pulls up record 5 pulls up record 5 adds/appends line to field badds/appends line to field b clicks update first clicks update second user b's line is added/appended to record 5, user a's line is overwritten not even seen. Ron -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED] Sent: Saturday, January 17, 2004 4:20 AM To: [EMAIL PROTECTED] Subject: Re: Do I need Innodb? On Fri, 16 Jan 2004 13:43:55 -0800 Ron McKeever [EMAIL PROTECTED] wrote: Scenario 1: I have noticed that if two users open a record to edit it in two different browsers and they edit the same field and then click update, the last user/browser gets written to the db, where the first users data is over written. I would hope it would write user ones info then write user twos info, without over writing user ones updates Will INNODB table type fix Scenario 1? No. You should use INSERT instead of UPDATE if you don't want the data to be overwritten. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
