本文介绍了mySQL - 表锁定与行锁定的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

应用说明

我有一个表,用于存储代表地图上区域的 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.

当用户尝试获取某些区域的所有权时,应用程序必须连接三个表并查询该地图内的所有已占用区域.如果他们中的任何一个被拿走,它应该拒绝他的所有权尝试.如果所有区域都可用,则应创建新区域并将相关区域添加到 tblAreas 中.

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 表,查询这些区域是否空闲,插入一个新领土及其区域,提交并解锁该表......或者 Areas 表应包含所有 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 in (...) 的事务都会锁定这些条目的索引.行本身是否存在并不重要.该锁将阻止另一个事务插入这些 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 - 表锁定与行锁定的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-03 21:23
查看更多