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:
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?