问题描述
应用程式说明
我有一个表格，在地图上储存代表区域的ID。每个地图包含1000个区域。 用户争取地图的不同区域的所有权。
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.
数据库设计
目前我有一张地图表，领土和一张地区表。
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
目前tblAreas只存储地图内的占用区域 - 它不包含每个地图1000记录，不管是否有人拥有它。
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.
问题：
我意识到我需要一个基于事务的系统，这样两个用户不会同时尝试拥有该区域。现在我可以看到，我必须锁定整个Area表，查询是否区域是免费的，插入一个新的领土和它的区域，提交和解锁表...或区域表应该包含所有1000每个地图和锁的区域应该应用于该地图的行。
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.
推荐答案
如果你有一个索引tblAreas.AreaID，那么任何包含 WHERE tblAreas.AreaID（...）的事务将锁定这些条目的索引。如果行自身存在与否无关紧要。该锁定会阻止其他事务插入这些ID的任何条目。所以我不认为你需要做任何你的建议。只要查询是否所有区域都可用于您的区域，将会获得您需要以原子方式插入您的区域的锁。
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.
这可能是一个问题，因为您的区域ID不是唯一的，因此在不同地图中具有相同ID的区域之间可能存在一些假序列化。它可能有助于添加mapID到您的tblAreas表，所以你可以使一个（mapID，areaID）索引来查找，这将避免索引上的错误冲突。 （这会使你的模式非规范化，你可能不想因为其他原因做这些。）
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.)
这篇关于mySQL - 表锁定行锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！