One of the DBAs here was playing around with MySQL. He'd heard that the InnoDB engine has rowlevel locking. So he created an InoDB table called cjg (his initials) with two columns, col1 is a number and col2 is text. He inserted a few rows. He set autocommit off. He then ran:
update cjg
set col1 = 2
where col2 = 1;
Then in another session he tried to insert the value (3, test) into cjg. It blocked.
He tried a variety of things, but was unable to get MySQL 5 to demonstrate that it knew how to do row-level locking.
So..can anyone come up with a demonstration where MySQL clearly does row-level locking? And can you explain why the example he tried to run locked the whole table?
Thanks,
Ben
no index...
ask on 2007-03-24T11:18:50
I had a longer reply, but somewhere between Firefox, use.perl and it being 4am I lost it.
In brief:
See
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.htmlInnoDB sets a lock on the index range that was scanned. No index = all rows.
If you add an index to the row you were using to update it should work.
This is also helpful:
http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.htmlSetting the transaction isolation level to read-committed is one of my "must do after installing and before starting" things. Search for InnoDB and sql_mode in the first "scalable something something" PDF you find on
http://develooper.com/talks/ - there are a couple of pages there with a couple of other useful options.
- ask
