database - mySQL - Table locking vs Row locking

Question

Application Description

I have a table which stores id's which represent area's on a map. Each map contains 1000 areas. A territory is any number of area's of a map that are touching. Users fight for ownership of different areas of the map.

Database Design

Currently I have a table of maps, a table of territories and a table of areas.

tblMaps: MapID, MapName

tblTerritories: TerrID (unique game wide), MapID, OwnerID, Status, Modified

tblAreas: AreaID (1-1000), TerrID

At the moment tblAreas only stores occupied areas within maps - it does not contain 1000 records per map regardless of if anyone owns it.

When a user tries to take ownership of some areas the application must join the three tables and query all taken areas within that map. If any of them are taken it should reject his ownership attempt. If all the areas are free a new territory should be created and the relevant area's added in tblAreas.

Problem

I realised that I need a transaction based system so that two users do not try to 'own' the area at the same time. Now as far as I can see I must either lock the entire Area table, query to see if the areas are free, insert a new territory and its area's, commit it and unlock the table... OR the Areas table should contain all 1000 areas of each map and the lock should just be applied to the rows of that map.

Hopefully there is a better option because as far as I can see. Locking the table will mean all area data is unaccessable for that second or with row locking the table is full of useless unoccupied areas.

Answer 1

If you have an index on tblAreas.AreaID, then any transaction that includes WHERE tblAreas.AreaID in (...) will lock the index for those entries. It does not matter if the rows themselves exist or not. That lock will prevent another transaction from inserting any entries for those IDs. So I don't think you need to do either of your suggestions. Just querying to see if all areas are available for your territory will get you the locks you need to insert your territory atomically.

This might be a bit of a problem as your area IDs are not gamewide unique, so there may be some false serialization between areas with the same ID in different maps. It might help to add mapID to your tblAreas table so you can make a (mapID, areaID) index to look up instead, which would avoid false collisions on the index. (That would denormalize your schema, which you may not want to do for other reasons.)