2

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.)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hamed
  • 279
  • 1
  • 3
  • 13
  • 2
    Why do you need to restart MessageId for each discussion? Just go with a global MessageId instead of a composite key. That will save you a lot of trouble and simplify operations like moving messages from one discussion to another. – Albin Sunnanbo Dec 04 '10 at 08:31
  • Yes. You're right. A global MessageId which is Identity solves this easier. I can't remember why I designed it that way! Thanks anyway. – Hamed Dec 04 '10 at 09:28

1 Answers1

2

Your transaction should be quite safe indeed - check out the MSDN docs on the SERIALIZABLE transaction level:

SERIALIZABLE

Specifies the following:

  • Statements cannot read data that has been modified but not yet committed by other transactions.

  • No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

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 IDENTITY is definitely much better!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • As you described earlier, composite keys will introduce bigger issues in maintenance period. I changed table design and honestly I don't know why I did that in the first place! Maybe I was too tired last night :-) Thank you for detailed description, information and tips. I appreciate that. – Hamed Dec 04 '10 at 10:32
  • 1
    I can't vote up. I don't have reputation. Sorry. Thanks again. – Hamed Dec 04 '10 at 10:34
  • 1
    @Hamed: you're quite welcome. You can upvote later, as soon as you have 15 rep points or more - shouldn't take long :-) – marc_s Dec 04 '10 at 10:43