SQL Server 插入论坛查询
考虑到论坛表和许多用户同时向其中插入消息，此事务的安全性如何？
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
DECLARE @LastMessageId SMALLINT
SELECT @LastMessageId = MAX(MessageId)
FROM Discussions
WHERE ForumId = @ForumId AND DiscussionId = @DiscussionId
INSERT INTO Discussions
(ForumId, DiscussionId, MessageId, ParentId, MessageSubject, MessageBody)
VALUES
(@ForumId, @DiscussionId, @LastMessageId + 1, @ParentId, @MessageSubject, @MessageBody)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
RETURN 0
END
ROLLBACK TRANSACTION
RETURN 1
在这里，我读取了最后一个 MessageId 并递增它。我无法使用身份字段，因为它需要为组中插入的每条消息递增（不是每条消息都插入表中）。
Considering a forum table and many users simultaneously inserting messages into it, how safe is this transaction?
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
DECLARE @LastMessageId SMALLINT
SELECT @LastMessageId = MAX(MessageId)
FROM Discussions
WHERE ForumId = @ForumId AND DiscussionId = @DiscussionId
INSERT INTO Discussions
(ForumId, DiscussionId, MessageId, ParentId, MessageSubject, MessageBody)
VALUES
(@ForumId, @DiscussionId, @LastMessageId + 1, @ParentId, @MessageSubject, @MessageBody)
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
RETURN 0
END
ROLLBACK TRANSACTION
RETURN 1
Here I read last MessageId and increment it. I can't use Identity field because it needs to be incremented for every message inserted in a group (not every message insert into table.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的事务确实应该非常安全 - 请查看 关于可串行化事务级别的 MSDN 文档< /a>：
可序列化
此事务隔离级别的主要问题是，它对服务器造成相当大的负载，并且序列化（顾名思义）任何访问，因此您的服务器性能和可扩展性将受到影响，例如，如果用户数量非常多，您将用户等待事务完成时可能会出现大量超时。
因此，使用更轻量级的全局消息 id 方法作为
INT IDENTITY肯定要好得多！Your transaction should be quite safe indeed - check out the MSDN docs on the SERIALIZABLE transaction level:
SERIALIZABLE
The main problem with this transaction isolation level is that it's a pretty heavy load on the server, and serializes (as the name implies) any access, so your server performance and scalability will suffer, e.g. with very high numbers of users, you'll possibly get lots of timeouts for users waiting for a transaction to finish.
So using the more lightweight approach of a global message id as
INT IDENTITYis definitely much better!