Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    6

    Unanswered: Transactions in Databases

    Hello Friends,
    I am currently working on a Mysql database and our architects are dead against using transactions in our queries. In order to solve transaction problems we are forced to use temp tables and flags which itself doesn't solve the transactional integrity problem by a lot but just minimizes it. Also our tables have no referential integrity built in. I was wondering if it is possible for any real world applications to function without using transactions and referential integrity.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes it is, you 'just' have to do the work yourself, you put an increased load on the network and on the server(s).

    A transactionin iteself is not some magical thing, you can replciate the transaction process yourself in code. it costs more to do, it costs more to test and validate, but the end product shoudl be the same.

    in my books the advocates for transactions houldbe your DBA's, after all they are supposed to care about the dat, supposed to care about data integrity...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2007
    Posts
    6
    Quote Originally Posted by healdem
    yes it is,
    I couldn't understand what this means? Are you saying there are real world apps out there that work without using transactions and use flags and temp tables. I come from a EJB background and I haven't seen a lot of apps like that.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what I meant was yes there are real world applications out there which don't use transactions, as in the DB transaction methods. they replicate the transactional processing normally offered by db engines by building those processes into the code. after all that's how it used to be done prior to the implementation of transactions into db engines.

    I can't say there are applications out there that use flags and temporary tables, but I do know of applciations in SQL that don't use RI and don't use transactions. in some cases the tools are there and the developer chose not use them, in some cases the developers didn't know the tools existed. why only they and/or the site knows.

    Personally I don't see the point in replicating code thats already tried, tested and verified by the supplier. Im not a believer in the "hair shirt" approach, where everything has to be done by the developer, and performance is measured by how much code you have written.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2007
    Posts
    6
    Quote Originally Posted by healdem
    what I meant was yes there are real world applications out there which don't use transactions, as in the DB transaction methods. they replicate the transactional processing normally offered by db engines by building those processes into the code. after all that's how it used to be done prior to the implementation of transactions into db engines.
    Why do they go through the pains of replicating what transactions does? Is there any reason to avoid transactions, other than the fear of poor performance. I don't think there any serious developers out there who doesn't know transactions exist. The senior developer who is opposed to transactions being used gave the reason that connections etc. might remain open and this might lead to issues in other parts of the application.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by salils
    I am currently working on a Mysql database and our architects are dead against using transactions in our queries. In order to solve transaction problems we are forced to use temp tables and flags which itself doesn't solve the transactional integrity problem by a lot but just minimizes it. Also our tables have no referential integrity built in. I was wondering if it is possible for any real world applications to function without using transactions and referential integrity.
    Perhaps it would be best if you tell us what type of transactional integrity problem you're trying to solve and then we can say what the best solution is. I've worked on loads of production databases which didn't use explicit transactions and they worked fine. It just depends on what you're trying to do and how you go about writing your SQL. If you're moving quantities from one record to another then yes, of course, it's best to use transactions. Keep in mind that using transactions too much may lead to deadlock problems.

  7. #7
    Join Date
    Mar 2007
    Posts
    6
    The problem is such. A whole table has to be replaced with a new set of data every day. computation of this data will take some time so the table can't be read-locked during this time. however the data is hierarchical in nature so if insertion of one of the rows fails then the whole operation must fail and must be returned back to old data. The way I am doing it right now is that i have active column that is set to 1 if the data is valid. When inserting data into the table then i insert it with active=0 and once insertion has successfully completed then the active flag is flipped 1 becomes 0 and 0 becomes 1 and then the rows with 0 are deleted from the table. if the flip query or the delete query fails then a message is emailed to the admin and he has to delete the rows manually. All this trouble just because we can't use transactions. The other option that i considered was using a temp table.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by salils
    A whole table has to be replaced with a new set of data every day.
    Do all the records change each day? If so then this seems a little odd to me - the table seems to be treated as if it was static data but it appears to be holding more transactional data.

    Quote Originally Posted by salils
    computation of this data will take some time so the table can't be read-locked during this time.
    So do all the work in a separate table - if it's really heavy processing done over a long time then it could even be done on a separate server.

    Quote Originally Posted by salils
    however the data is hierarchical in nature so if insertion of one of the rows fails then the whole operation must fail and must be returned back to old data.
    Just insert the new records (calculated and held in the temp table) into the original table with the active flag set to 0. Then do your update to flip all the active flags. I assume you currently flip the 0 to 1 and the 1 to 0 in a single update otherwise you have the same risk of failure Then delete the records where active is 0. Clear the "temp" table if it's a permanent table. If it's only a small % of the records that change then you could simply delete, update and insert these records in - assuming it's fine to have the records that were going to be deleted gone before the other updates/inserts get done.

    You should also store a status flag on your system to say where in the process you are ie started, copied data, processed, reinserted, flipped flags, finished. This means that if the batch needs to be restarted then it could skip the parts it's already done and then continue with it's work. Now, if the process does die, then it's simply a question of restarting the batch and letting it work out what to do rather than worry about getting called at 4am to run deletes on a live database. You could then get the batch to restart automatically if there's any issues.

    Quote Originally Posted by salils
    All this trouble just because we can't use transactions.
    Using transactions would make the process simpler but it does increase the chances of locking issues - even more so if your process takes a long time to complete - so the DBA's are simply worried that with transactions you'll have an easy ride programming things and then they'll end up getting called at 4am to fix all the deadlocks.

    Another option would be to rename the tables so the original table becomes the "temp" table and the "temp" table becomes the live table.
    Last edited by mike_bike_kite; 01-22-09 at 06:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •