查询Lobby Server卡死后对于CashOut的影响的DB脚本

最新推荐文章于 2025-04-22 13:46:19 发布

nova_pegasus

最新推荐文章于 2025-04-22 13:46:19 发布

阅读量755

点赞数

CC 4.0 BY-SA版权

文章标签： server null date table join 脚本

本文链接：https://blog.youkuaiyun.com/nova_pegasus/article/details/6973708

文章详细记录了LobbyServer在上周末因WS没有返回问题导致卡顿，进而影响Cashout记录的情况。通过分析日志和使用特定SQL查询及存储过程，作者提供了准确统计受影响人数的方法，确保了数据的完整性和准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Lobby Server因为WS没有返回的问题，上个周末(11月5号和6号) Lobby Server卡住了两次。虽然后来通过重启解决了问题，但是不可避免的结果是，Cashout的值会少记不少。

我已经让Bob同学查了一下日志，他大概给了一下出事的时间里在出事的房间里的人数：

room:16 (2011-11-05 18:58:02重启)
2011-11-05 18:26:24 10 player in lobby server

room:11 (2011-11-05 15:32:06、2011-11-05 18:18:02重启)
2011-11-05 13:49:37 18 player in lobby server
2011-11-05 17:24:33 26 player in lobby server

但是应该有准确的统计方法，先来看用到的CrossTransactionHistory的表的结构：

Schema of CrossTransactionHistory Table

用下面的SQL来寻找在一定时间范围内带入次数比带出次数多的玩家信息：

SELECT TIn.UserName, TIn.InTime, TOut.OutTime, TIn.SumMoney AS InMoney, TOut.SumMoney AS OutMoney, 
	TIn.SumMoney AS SumIn,TOut.SumMoney AS SumOut, TIn.FirstInTime, TOut.FirstOutTime, TIn.LastInTime, TOut.LastOutTime
FROM
	(SELECT UserName, COUNT(TransactionType) AS InTime, sum(Money) as SumMoney, min(TransactionTime) AS FirstInTime,
		max(TransactionTime) as LastInTime
		FROM CrossTransactionHistory_TEMP_1
		WHERE RoomID = 11
			AND TransactionTime BETWEEN DATE('2011-11-05 15:00:00') AND DATE('2011-11-07 12:00:00')
			AND TransactionType = 0
			AND ErrorCode = 0
			GROUP BY UserName
			ORDER BY UserName DESC) AS TIn
LEFT OUTER JOIN 
	(SELECT UserName, COUNT(TransactionType) AS OutTime, sum(Money) as SumMoney, min(TransactionTime) AS FirstOutTime,
		max(TransactionTime) as LastOutTime
		FROM CrossTransactionHistory_TEMP_1
		WHERE RoomID = 11
			AND TransactionTime BETWEEN DATE('2011-11-05 15:00:00') AND DATE('2011-11-07 12:00:00')
			AND TransactionType = 1
			AND ErrorCode = 0
			GROUP BY UserName
			ORDER BY UserName DESC) AS TOut
	ON TIn.UserName = TOut.UserName
WHERE TIn.InTime > TOut.OutTime
ORDER BY TIn.UserName;

再用下面的存储过程用来检查这个玩家是不是真的出现了连续两次带钱进桌的情况：

DROP PROCEDURE IF EXISTS CheckCashOut;
CREATE PROCEDURE CheckCashOut(IN UName varchar(64),IN RID int(10))
	BEGIN
		DECLARE RCount int;
		DROP TABLE IF EXISTS CheckCashOut_Temp;
		/*添加一个临时表*/
		CREATE TABLE CheckCashOut_Temp (
			ID bigint(20) NOT NULL AUTO_INCREMENT,
			UserName varchar(64) NOT NULL,
			RoomID int(10) NOT NULL,
			TransactionType int(11) NOT NULL,
			TransactionTime datetime NOT NULL,
			PRIMARY KEY (ID)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		/*向临时表中插入按需要排列的数据列*/
		INSERT INTO CheckCashOut_Temp (UserName, RoomID, TransactionType,TransactionTime)
			SELECT  UserName, RoomID, TransactionType, TransactionTime
			FROM CrossTransactionHistory_TEMP_1 
			WHERE TransactionType IN (0,1)
				AND UserName = UName
				AND RoomID = RID
			ORDER BY TransactionTime;
		/*检查相邻的两列中的TransactionType是否是一样的*/
		/*即是否有连续两次的带入记录*/
		SELECT A.ID, B.ID, A.TransactionType, B.TransactionType
		FROM CheckCashOut_Temp A, CheckCashOut_Temp B
		WHERE ABS(A.ID - B.ID)=1
			AND A.ID <= B.ID
			AND A.TransactionType = 0
			AND A.TransactionType = B.TransactionType;
		SELECT FOUND_ROWS() INTO RCount;
		SELECT RCount;
		DROP TABLE IF EXISTS CheckCashOut_Temp;
	END;

一个用户一个用户地检查，如果真的出现了就算是一个。这样得出的人数应该是比较准确的。