Hello,

I have just begun to use mysql for a project I'm working on. mysql is
running on one machine, and on another, there are multiple instances of a
mysql client that repeatedly (and quite frequently) connect, query, and
disconnect from the mysql server.

The problem is, sometimes these queries that would conflict if they weren't
atomic happen at almost the exact same time, and mysqld crashes and
restarts. Here are some examples from the logs:

13313 Connect root@whatever.com on mp3share
13314 Connect root@whatever.com on mp3share
13313 Query DELETE FROM music WHERE
ownername="whatever"
13314 Query SELECT count(*) FROM music
Here, two different clients have connected and almost simultaneously
queried. According to everything I've read, this should not be a problem!
But it is. After this mysqld crashes and restarts.

67863 Connect root@whatever.com on mp3share
67863 Query LOCK TABLES music WRITE, login WRITE
67863 Query DELETE FROM music WHERE
ownername="whatever"
67864 Connect root@whatever.com on mp3share
67864 Query SELECT * FROM login WHERE ip != ""
67863 Query UPDATE login SET ip="" WHERE
user="whatever"
67863 Query UNLOCK TABLES
Here, I tried using write locks. But apparently connection 67864 could care
less about that!
mysqld crashed and restarted right after the UNLOCK TABLES.


1890 Connect root@whatever.com on mp3share
1890 Query LOCK TABLES music WRITE, login WRITE
1890 Query DELETE FROM music WHERE
ownername="whatever"
1891 Connect root@whatever.com on mp3share
1891 Query LOCK TABLES music READ
1890 Query UPDATE login SET ip="" WHERE
user="whatever"
1890 Query UNLOCK TABLES
So, I tried using both READ and WRITE locks on EVERYTHING I did. Exact same
problem!

Here is what shows up in the error log after a crash and restart like I
describe:
Number of processes running now: 1
mysqld process hanging, pid 8016 - killed
mysqld restarted on Mon Mar 6 16:37:27 EST 2000
/usr/sbin/mysqld: ready for connections

I thought any single query statement was atomic? What's going on here??

Clearly I am doing something wrong. I have no idea what. It's probably
really dumb. Thank you very much in advance for any help anyone might be
able to give.

Thanks,
Shawn