Placed At : MAATDLN
Hi,
I have a database with a table that has 3 columns - name, age and status
and this table has 10 rows.
This table is an Innodb table with dyname table type (incase this is
important) and so I can conduct transactions on this table.
I using a client open a connection to this database and start a
transaction, the first of my statement being
a)delete from <table-name> where name='xxx';
Now I dont either commit or rollback my transaction and on a second window
using a client again connect to the database and give a statement - the
statement being
b)update <table-name> set status='Y' where status='N';
Now this query hangs and it returns stating lock wait timeout exceeded.
I go one step further and give another query
c)update <table-name> set status='Y' where name='bbb' and this works like a
dream (as expected).
Normally one would have expected the second one also to have worked because
Innodb has row level locking on not table level locking but I was quite
surprised that it didnt work. Now I know if it had been table leve locking
then my third statement would not have worked but it worked...hence it
almost confirms that innodb is indeed using row level locking, but is there
any way I can get my second statement to work too without me having to wait
until I complete the transaction that I started on my first window.
Is this condition normal cos this works on both Oracle and Post gres.
Regards
Raghu
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
