Grub/Bob:
Some comments based on my experience...
If the isolation is set to read committed, transaction #2 would not see the msg_number used by the uncommitted trx #1, and uses the same msg_number. A duplicate sequence occurs.
-You can avoid this by doing 'dummy update' at the very beginning of transation #1 on the data that determines the next msg_number. This will cause transaction #2 to wait until that 'dummy update' is committed/rolled back. if your stored proc is lean and quick, it should not have an impact.
-ideally, sequence numbers (msg ids, board-ids, etc) should be stored in a separate table, that way you can preform the technique described above only on this table and way from your main data tables. One of the field on this table could be an 'update_date' field, for example, that you can update at the very beginning of transaction 1
-you obviously would want to keep your transactions small if you are employing locking.
-even though this forces a lock on a row that controls the msg_number, i think it may actually solve a lot of the problems you all are seeing...and done correctly, i don't think you will see any noticable difference in performance. I personally WOULD AVOID the "dirty reads" (isolation level = read uncommitted)...that in my opinion, is risky and you have to really have robust error handling to avoid some potential issues. On larger systems it can also lead to some kludgy code.
AI
