#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    60
    Rep Power
    5

    Cftransaction & mySQL table locking


    I have a series of mySQL query updates within a cftransaction driven by Flash code and I need to lock the relevant tables to prevent other users from updating whilst this transaction is in progress. So, prior to the cftransaction statement, I execute a query:

    LOCK TABLE tableA WRITE, tableB WRITE, ...etc

    and following the /cftransaction, and other query:

    UNLOCK TABLES.

    it works fine. ne problem is that there seem to be no way to test whether I have actually obtained these locks. As a test, I used the query browser to lock tableA and then I executed my Flash transaction. On screen it didn't update and eventually traced:

    Error opening URL "http://devel:8500/flashservices/gateway/"

    Checking the database, all tables EXCEPT tableA were updated. Not what I would have hoped. So, how can I achieve my objective - any thoughts?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,243
    Rep Power
    967
    As far as I know, you want to be using cftransaction with the appropriate isolation level. I really have no idea how the MySQL lock table works. For more information on isolation level you might look at http://en.wikipedia.org/wiki/Isolati...abase_systems).
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    60
    Rep Power
    5
    kiteless, thank you and I'll read-up on isolation level.

IMN logo majestic logo threadwatch logo seochat tools logo