In my previous blog I added some simple twitter integration to tweet the entries on I’m never going back there. This was being done in same thread as the users http request – which is not great as the API calls to twitter could be slow or fail.
So, my 30 minute task this week was to move it to a background thread.
[more]
Design
The design is fairly simple:
- Create a flag on the comment to indicate if it has been tweeted.
- Write code that retrieves all un-tweeted comments, tweets them and updates their status.
- Execute this code in a background thread as each comment is added and on application start up.
Without some locking (such as a distributed transaction with serialized read isolation level) this could lead to duplicate twitter entries if two comments were submitted simultaneously.
However, twitter does not allow duplicate tweets so the second tweet should be rejected by twitter.
Database changes
Since we’re now in production we need to script our database changes.
Adding extra column and changes to insert stored procedure:
ALTER TABLE tblComment ADD bTweeted bit not null default 0
GO
ALTER PROCEDURE [dbo].[usp_CommentInsert]
(
@p_vcrComment nvarchar(1000),
@p_intVenueId INT,
@p_vcrUserUpdated MediumString
)
AS
DECLARE @ident INT
INSERT INTO tblComment VALUES(@p_vcrComment,
@p_intVenueId,
1,
@p_vcrUserUpdated,
GetDate(),
GetDate(),
0)
SET @ident = SCOPE_IDENTITY()
SELECT @ident
GO
We will also need to update our comments -
CREATE PROCEDURE usp_CommentUpdate
(
@p_intCommentId INT,
@p_vcrComment nvarchar(1000),
@p_intVenueId INT,
@p_vcrUserUpdated MediumString,
@p_tweeted bit
)
AS
UPDATE tblComment
SET vcrComment = @p_vcrComment,
intVenueId = @p_intVenueId,
vcrUserUpdated = @p_vcrUserUpdated,
dteLastUpdated = GetDate(),
bTweeted = @p_tweeted
WHERE
intCommentId = @p_intCommentId
GO
GRANT EXEC ON usp_CommentUpdate TO VICTOR
GO
And fetch un-tweeted comments:
CREATE PROCEDURE usp_CommentGetUntweeted
AS
SELECT * from tblComment c
INNER JOIN tblVenue v
ON c.intVenueId = v.intVenueId
INNER JOIN tblVenueType t
ON v.intVenueTypeId = t.intVenueTypeId
WHERE (c.bTweeted = 0 AND blnApproved=1)
GO
GRANT EXEC ON usp_CommentGetUntweeted TO VICTOR
GO
Changes to the Business objects
The Comment class has an extra attribute to indicate if it has been tweeted.
Changes to the Data Access Layer (DAL).
The data access layer changes :
- Populate the new attribute on the class as the object is created from a data reader (amended create method described in previous blog "Ditching the Entity Framework".).
- Expose methods for the new update and search stored procedures.
Changes to the Business Processes
A couple of new methods here to handle the threading -
public static void TweetCommentsAsync()
{
ThreadPool.QueueUserWorkItem(new WaitCallback(TweetComments));
}
public static void TweetComments(Object stateInfo)
{
ICollection<Comment> commentsToTwwet = DBComment.GetUntweetedComments();
foreach (Comment c in commentsToTwwet)
{
TweetComment(c);
}
}
The TweetCommentsAsync() method will sets the TweetComments method to be called on a thread from the thread pool. The runtime controls these threads, normally limiting the number of active threads at any one time to prevent too much context switching. I believe 25 threads per CPU used to be the normal allocation in ASP.NET – but this is from memory.
An overloaded version of ThreadPool.QueueUserWorkItem allows data to be past to the method to be invoked.
Obviously the TweetComment method is also updated to update the database.
…
TwitterResponse<TwitterStatus> resp = TwitterStatus.Update(tokens, tweet);
if (resp.Result == RequestResult.Success)
{
// update teh comment
c.Tweeted = true;
UpdateComment(c);
ret = true;
}…
Obviously we call the TweetCommentsAsync each time a comment is added and also from the application start-up code in the Global.asax
Conclusion
ThreadPool.QueueUserWorkItem provides a simple, safe mechanism of performing asynchronous operations for example, when we want a thread to process as quickly as possible to optimise user responsiveness.
