Raghu, ----- Original Message ----- From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, March 05, 2003 7:03 AM Subject: Peculiar Problem During Transactions
> > 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'; please see section 8.4 at http://www.innodb.com/ibman.html#InnoDB_transaction_model: " UPDATE ... SET ... WHERE ... : sets an exclusive lock on every record the search encounters. " The search in your UPDATE is probably not using any index but scanning the whole table. The need to lock every record we look at in an UPDATE stems from MySQL's binlogging. Binlogging requires that data-modifying SQL statements are performed at a SERIALIZABLE isolation level with respect to each other. That in turn is needed because we must be sure a replication slave performs the SQL statement in the exact same way as the master. > 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). This uses an index and does not scan the whole table. > 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 Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query --------------------------------------------------------------------- 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
