I’m still quite new to Mysql, although I’ve worked with a lot I’ve never worked on a large scale site that could in theory of many interactions on the same table occurring simultaneously. As such, I’m curious what happens if two scripts execute at the same time and both want to modify table.
Could someone explain what might happen in these scenarios:
Script A wants to insert into table 1, Script B wants to insert into table 1
Script A wants to insert into table 1, Script B wants to delete from table 1
Script A wants to edit a record in table 1, Script B wants to delete the same record in table 1
Script A wants to count records in table 1, Script B wants to delete or insert into table 1
I have a feeling I need some kind of mysql row locking or something but just wanted to make sure before I began researching. Thanks for your help guys.
One being that MyISAM does only full table locking
As I see it (and I’m no database guru by any means)
1 - might be no problem
2 - might be no problem
3 - Script B will have to “wait”
4 - might be no problem
Not very helpful I know.
The “mights” depend on whether or not it is the same row.
If it is, Script B will need to “wait”, if a different row it won’t.
The “wait” is most likely negligible. Is this theory or an actual pressing need?
Hopefully @r937 or @rpkamp will be willing to provide some insight?
I’d be interested to know at what ball-park figure of “hits” this should be of a major concern.
You might want to look into the use of transactions. I know that MyISAM doesn’t support them and Innodb does support them, i don’t know about the other storage engines