Our application started giving us lots of problems with increase in concurrent transactional activity. After lots of hours wasted debugging the problem and implementing temporary (inefficient) work-arounds in the application, I would like to share my experience with the community here.
The following issue in InnoDB was identified and is forcing us to migrate our transaction database to Sybase:
From InnoDB documentation:
"InnoDB uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really ``atomic''; they automatically set locks on the (possibly several) index records of the row inserted or deleted."
Because row-level locking is not 'atomic' in nature, this is crippling our ability to use InnoDB in an enterprise production enviroment. Anyone having similar issues should comment here to raise concern over this critical issue.
This is quite unacceptable in production environments. InnoDB should support 'atomic' row-level locking, otherwise it will become quite useless for large-scale deployments which require concurrent transaction and reliable locking.
------------------------------
Afroze Husain Zubairi
Kolachi Advanced Technologies
http://www.kolachi.net
------------------------------
NOTICE - This message contains privileged and confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message, you are hereby notified that you must not disseminate, copy or take any action in reliance on it. If you have received this message in error, please immediately notify Kolachi Advanced Technologies, its subsidiaries or associates. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the view of Kolachi Advanced Technologies, its subsidiaries and associates.