SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jun 2002
    Location
    Barcelona, Spain
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL/Innodb transactions & locking

    I am trying to get transaction processing & row locking working with MySQL.

    I have copied the my-medium.cnf sample file to my.ini, uncommented all the innodb related lines and set the directories appropriately and created the directories. MySQL has created the tablespace and log files in those directories. In short, innodb is up and running.

    But...when I open two client sessions simultaneously, no matter what combinatation of locks, "autocommit", "begin work" I use, the sessions do not recognise the locks/transactions of the other.

    Example
    Client A> set autocommit=0;
    Client A> begin work;
    Client A> select * from table;

    Client B> set autocommit=0;
    Client B> begin work;
    Client B> select * from table;

    Client A> Update table....;

    Client B> select * from table;

    in this last select, I expect client B to see the same result as the first time they ran the query, because client A has not committed... but in reality, I see client A's updates. Similarly, locks that client A places are not recognised by client B.

    All the articles I have read indicate that client B should not see A's updates, and they don't mention any special config seetings I need to add. I did see a "SkipLocks" value (or something like that) in the cnf file, so removed that. No effect.

    Help please!!

  2. #2
    SitePoint Wizard silver trophy TheOriginalH's Avatar
    Join Date
    Aug 2000
    Location
    Thailand
    Posts
    4,810
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    While anything can be posted in the beginners forum, I feel that this question is prolly above that level somewhat. I'm sure you'll get a response here..

    As for me....

    I enjoyed watching the ascii glide gracefully over my head
    ~The Artist Latterly Known as Crazy Hamster~
    922ee590a26bd62eb9b33cf2877a00df
    Currently delving into Django, GIT & CentOS

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds broken to me:
    http://www.mysql.com/doc/I/n/InnoDB_...d_example.html

    I think, perhaps, you must explicitly state you want them to be locked in some fashion like this:
    http://www.mysql.com/doc/I/n/InnoDB_locking_reads.html

  4. #4
    SitePoint Member
    Join Date
    Jun 2002
    Location
    Barcelona, Spain
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Doh! Now locks are working too well!

    Thanks for the feedback Matt. Turns out I've been a bit of a donkey. Although my system claimed to support Innodb, and I created tables with the type=innodb flag, because I had not uncommented the innodb lines of my cnf file, they still weren't supporting transactions. Modified the cnf, recreated the tables, now transactions & locking work. Too well.

    So, here is another essay for anyone with the stamina and patience to go through it . Now my testing shows that whole tables are being locked when I thought I was only locking rows.

    Example:
    User A> set autocommit=0;
    User A> begin;
    User A> select row1 from table lock in share mode;

    User B> set autocommit=0;
    User B> begin;
    User B> select row6 from table lock in share mode;
    //NO! doesn't let me.
    User B> insert into table values(...);
    //NO! doesn't let me.

    In fact, all user B can do is select, or select with in share mode. No other operation is enabled on the whole table. I thought only row 1 would be locked.

    Anyway, I am left with 2 questions:
    1) what have I misunderstood/done wrong/forgotten to configure?
    2) is a "for update" lock not meant to prevent others from even selecting the locked rows? My tests indicate that the only difference between share and update locks is that the latter prevents others from placing share locks. Makes sense, but its not what I understood.

    Bonus Question:
    Its proving more difficult than I expected to find the key combinateion to recall the previous command in the mysql command line client. I found a suggestion that it is Ctrl-p, but that just gives me a little arrow character. Pretty, but pointless. Also I'd like to now how I can move the cursor around the current command line so that when I realise I have made a type, I can correct. Maybe I am being too optimistics here?

    Thanks for lasting this long. All help appreciated. I am only slightly stupid and not completely incompetent, so don't let that put you off helping.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Only one row should be locked, provided you are doing a point query, e.g. WHERE rowid = 124

    What is your table structure, and your query? Remember if you have no indexes on a particular WHERE argument it will table scan and hence table-lock.

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    At least in my MySQL shell I can use the UP arrow to repeat commands.

    And by the way, welcome to the forums!

  7. #7
    SitePoint Member
    Join Date
    Jun 2002
    Location
    Barcelona, Spain
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aha! That will be it! The tables I tested with are not indexed! How did I miss that detail? Thanks for your reply again, I owe you one.


    As for recalling the last command, no luck. At least my fingers will get some excercise.

    Cheers

    Gary

  8. #8
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you using MySQL on Windows.

    Use mysqlc to start the shell that supports recalling commands.

    You might need to place some cygwin library in your windows dir.

  9. #9
    SitePoint Member
    Join Date
    Jun 2002
    Location
    Barcelona, Spain
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's it - Thanks Chris! Ctrl-P now recalls previous commands.


Bookmarks

Posting Permissions

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