I have about 30 or so people using the site to add/edit/delete from multiple tables.

I don't lock the table when writing to them. Is this going to cause a problem if two people try to write to the table at the same time.

Should I put a lock on every query to the database?

John

Thanks for the input

Interesting question... The MySQL manual (http://www.mysql.com/doc/en/Table_locking.html) gives some insight w.r.t/table locking.

Personally, I wouldn't lock the tables for every single write unless the database was EXTREMELY loaded.

Just my $0.02.

Mostly there will be select queries. Writing to the table would happen rarely and there wouldn't be a lot of data either.

So for now I will not use the lock. What is the worse case scenario?

MySQL's handling of INSERTs/UPDATEs/etc is very robust... Unless the host machine crashes during a write, there should usually be no corruption.

In extreme cases, where 2 (or more) people are writing to the same row without locking in place, MySQL will try to handle that. If it's unable to gracefully complete both writes, you could use myisamchk to repair the tables.

I re-iterate: for a low-volume database you shouldnt really need locking. If you're planning on scaling it up, then locking should be in place. Otherwise, leave it as it is.

Heh, you are lucky. MySQL improved.
http://www.mysql.com/press/release_2002_11.html

I had to convert couple projects to PosgreSQL when I needed transaction support.

There is almost no reason to lock a DB unless you are writing a critical update that will take a great deal of time to complete that needs to be completed before the next query takes place.

Other than that, MYSQL does the query handling que behind the scenes very well...

Multiple Select statements have no content affect on the DB so I wouldn't worry about concurrent multiple hits since there is no altering of data all the user will be pulling the same data.

If I'm not mistaken about MySQL's query table INSERT's have a slightly greater priority over SELECT statements but, I'm not entirely sure.

There is a MySQL statement called "INSERT DELAYED" that will wait until a thread is open to do the write.

http://www.mysql.com/doc/en/INSERT_DELAYED.html