MySQL/Innodb transactions & locking

[Gary Bauer]

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!!

[TheOriginalH]

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

[MattR]

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

[Gary Bauer]

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.

[MattR]

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.

[MattR]

At least in my MySQL shell I can use the UP arrow to repeat commands.

And by the way, welcome to the forums!

[Gary Bauer]

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

[Chris82]

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.

[Gary Bauer]

That's it - Thanks Chris! Ctrl-P now recalls previous commands.