CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 MYSQL Transaction Control [RESOLVED]

Print topic Send  topic

Author Message
acedrummond


Posts: 90
Posted: 12/24/2012, 11:55 AM

I have a situation where I have a table of master participant records with a participant id key (3 digits).
I also have a table of item detail records where the key is a concatenation of participant id + an incremented relative number for each item.

End result is item key is ppp###.

I keep the last relative number for each participant in the master participant record and update that number each time I insert a record into the detail item table.

The problem I've experienced is that sometimes the user gets hung up in the process (for reasons unexplained so far) and the update does not complete to the master participant record. This leaves me in a situation where that user may lose a detail record or be trying to insert a duplicate into the detail items since the master was not updated.

I've spoken to some users and it seems like their connection was lost or the local PC hung, but i can't be sure.

So I am trying to add transaction control to the application to alleviate the problem.

It appears I want to add "START TRANSACTION" and "COMMIT" statements so that if the transaction does not complete no detail records get written and no update to the master participant takes place.

So I placed BEFORE EXECUTE INSERT

$db = new clsDBConnection1();
$SQL = 'START TRANSACTION';
$db->query($SQL);

and AFTER INSERT

$db = new clsDBConnection1();
$SQL = 'COMMIT';
$db->query($SQL);

While it gives no error it does not seem to work if I place an EXIT; prior to 'COMMIT' for purposes of testing although if I try to access with phpmysql to delete the record after trying the exit it appears locked as phpmysql hangs until I reboot!

I think I'm missing something, can anyone steer me in the right direction please?

Environment: MYSQL 5.5.24 ccs 5 latest update, WIN 7 Home Premium, php 5.3.13 and apache 2.2.22

_________________
Ace Drummond
View profile  Send private message
bannedone


Posts: 273
Posted: 12/24/2012, 12:22 PM

Hi

Would something like this help you?

http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples

As it relates to CCS

you might try starting the transaction in the before execute select

Then in the after insert test for success of the insert then commit or rollback

I do nt think you should use exit as I suspect that is what is causing your lockup issue.

Also I think CCS closes the connection after all queries by default. You might need to configure your DB connection to be persistent in the connection setup. By doing this you might need to handle all $db->close() yourself

Just guessing here.

Let us know if you resolve this issue.

8-)

_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2
View profile  Send private message
acedrummond


Posts: 90
Posted: 12/26/2012, 3:31 PM

Your comments appreciated; however, my problem is not an error I can trap vs. some odd incident where the transaction is aborted.

If, in fact, I try to do a rollback it works perfectly. In my scenario the transaction does not finish for some reason outside my control and I do not want that transaction committed since it did not complete.

I found the following link which tries to explain what should happen in layman's terms http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/5/

It stated that if there is no 'COMMIT' then the transaction is not posted.

I've done a little more testing and found a few things:

1. The participant master record is NOT updated and the item transaction is not posted.
2. The hang I experience is due to an index on the item table that WAS UPDATED event though the transaction did not complete and no detail record is in the item table!
3. The problem does not affect other users logged in.
4. I've found that if I reboot the server then the problem account is OK as miraculously the index gets corrected!

So at this point I know that start transaction and commit or rollback work as advertised; however, when an index is on a table it seems to have a problem with an aborted transaction!

I will keep testing and advise. I assume next step is to remove the index as it is not critical component.
_________________
Ace Drummond
View profile  Send private message
acedrummond


Posts: 90
Posted: 01/04/2013, 10:55 AM

I've been able via a lot of experimenting to find a way to make transaction control work for me.
PHP - MYSQL in use.
The basics used for inserting records:

ODBC Connection with PERSIST CONNECTION -NOT- Checked!
'
BEFORE EXECUTE INSERT add 'START TRANSACTION'

example: $db = new clsDBConnection():
$SQL = 'START TRANSACTION';
$db->query($SQL);

AFTER EXECUTE INSERT add 'COMMIT'

example: $db = new clsDBConnection():
$SQL = 'COMMIT';
$db->query($SQL);

If the transaction aborts before 'COMMIT' nothing gets posted to the database.

You can also add a programmatic 'ROLLBACK' (after execute insert) if some logic in your program detects something that would make you not want to post a transaction.

example: $db = new clsDBConnection():
$SQL = ROLLBACK';
$db->query($SQL);

I'm sure you can apply the same principle to updates as well although I have not done so.

I hope this helps others as this is a very important capability and should be quite useful to others.

_________________
Ace Drummond
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.