SQL Server 2008 R2, SQL TABLE, When multiple users update the same field in the same row at the same time?
Posted on 2014-11-14
The problem is we let each user of that system have their own read and unread email flags.
We first approached this problem by creating a tblMessageReadByUser table which contained a user record for each and every email that came in. Which you can guess can grow really fast.
Our second approach was to create a separate tblMessageState table, one record for every email. Which contains a string of 1024 1's and 0's and based on user id each user "byte" would be marked as 1 or 0. (we originally thought byte array field but there is a 32 limit and there could be more users than that.
But even this solution isn't 100 percent solid, there is funkiness when the different users read or unread the same email updating the same field at the same time.
Any thoughts or suggestions on the optimal way to handle this scenario? We are upgrading to SQL Server 2014 next year if helps.