Hello. First, my apologies, if my answer sounded too harsh. It wasn't meant personally, but after answering ten or more mail with too few details, one gets somehow bored, you know. ;-)
On Tue, Apr 30, 2002 at 09:29:41AM +0100, [EMAIL PROTECTED] wrote: [...] > and referred by date to the original posting (26/04/2002 11:31) which > did contain more details. [...] > I did quote the error in the original posting... [...] > Again, the table type is mentioned in the original posting. I am using I don't have your first post anymore. Only the second one, to which I replied. As probably everyone here, I delete read postings. (And no, I do not search the archive in order to answer questions here.) I admit, as I read your post again, I see some hints that your first posts contains more detail. > I would have thought that the comment in the script describing how to > create the "test" database -- "CREATE TABLE x (id INT) TYPE=BDB" -- > would have given the game away there anyway... Did overlook that. You did even mention it at the beginning, too- Regardless, in several years of peer-review I have learned not to trust comments when strange problems arise. Well, learned my lesson of today. ;-) Now back to your problem: > >Well, this time I start asking, instead of guessing again: Which table > >type (MyISAM/InnoDB/BDB) do you use? Do you intend to use transactions > >or not? > > I am using BDB tables, and I am making use of transactions. Okay, than that is the reason for the deadlocks and it is to be expected, as far as I understand (as writers are involved): http://www.mysql.com/doc/B/D/BDB_characteristics.html BDB tables use optimistic locking, i.e. it is assumed that you are lucky, and no one else will need the same locks as you do. If you conflict with other locks, one of your will gets an deadlock error. (From http://www.sleepycat.com/docs/ref/transapp/put.html - did not find an explanation in the MySQL manual :-( ) This happens rather seldom, as BDB uses page locks, which only block a small part of the table. > >A possible reason that you observe the rollback one time and not the > >other could be that the mysql client sets auto-commit differently? > > Both clients are instances of the same program running on the same > machine, so I don't believe they are setting auto-commit differently. Sorry, I meant the Perl vs. the command line client. I.e. Did you assure, that the command line client also does not auto-commit, when you run the INSERTs? I am not sure if you mean exactly this by your next comment, because you interpreted my former statement differently. > Repeating the exercise explicitly setting "auto-commit=0;" in each of > them makes do difference either. Okay, now we know the fact that the processes may deadlock. The question is why it does not happen in every environment. That it does not happen with Sybase could simply mean, that Sybase uses a different locking approach or is able to lock inserts in a way that would never deadlock. I don't know Sybase good enough to comment on this. Well, that DBD::ADO and the mysql client work could mean, that auto-commit is enabled - could you check that it is in fact disabled on the server side by quering the value... hm. I don't find a way to query the status of a variable set via SET. Well, then how about trying to create a deadlock by purpose (using an explicit LOCK TABLE for the first client and an update for the second or so...). Hm. Maybe I am completely wrong and MySQL/BDB works in a way that INSERTs of two clients are distributed to pages so that they don't conflict (i.e. starting a new page for the second client), but I couldn't find any hint on that in one of the two manual. Bye, Benjamin. [EMAIL PROTECTED] --------------------------------------------------------------------- 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
