I'm developing a website, so when a user hits a button the following sample sql code is run. This is for registeration and basically meant to take information entered in a form and save it.

BEGIN TRAN
*sql code*
*sql code*
COMMITT TRAN 

BEGIN TRAN
*sql code*
COMMITT TRAN 

IF *sql code*
BEGIN 
ROLLBACK TRAN
END

in the above code there are several transactions - information being inserted into several tables, if not all goes as planned we rollback the transactions. But if someone presses the button for this script to execute then suddenly hits the 'x' or stop execution button on their browser; leading the execution of the code to stop and never reach the point where the transaction can be rolled back what can we do then?

This will mean the database can often end up with tables that have half filled columns or some linked tables where table X that is linked to table Y doesn't contain any information. Can this kind of thing even happen? Sorry I'm not too well versed in sql 'transactions' at the moment so any help would be nice.

Why don't you just delete lines 4, 6 and 8 then add an ELSE COMMIT TRANSACTION after line 13? That puts everything in the same transaction.

Member Avatar for LastMitch

@asif49

in the above code there are several transactions - information being inserted into several tables, if not all goes as planned we rollback the transactions. But if someone presses the button for this script to execute then suddenly hits the 'x' or stop execution button on their browser; leading the execution of the code to stop and never reach the point where the transaction can be rolled back what can we do then?

This will mean the database can often end up with tables that have half filled columns or some linked tables where table X that is linked to table Y doesn't contain any information. Can this kind of thing even happen? Sorry I'm not too well versed in sql 'transactions' at the moment so any help would be nice.

Can you actually show some of the code with the query?

If you don't understand how the query works and you also don't have a code for it then here are couple of sites that provide some MySQL Transactions code

Read this:

http://www.phpknowhow.com/mysql/transactions/

or this:

http://tympanus.net/codrops/2009/09/01/using-mysql-transactions-with-php/

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.