none
setting Isolation levels

    Question

  • We have a window service which is responsible to send SMS to users.

    We have 20 instance running as a window service which hits a single table “Tab1”.
    I have implemented isolation level – “SET TRANSACTION ISOLATION LEVEL SERIALIZABLE”, but it is not working because 20 instance is reading a single record at the same time and thus sending 20 SMS to single user.

    I want that if 20 instances read the same record, only one should send the SMS and not all.
    Please advice what can be done..

    We have these 20 instances to speed up the process of sending SMS from the table... so we cannot just rely on one service

    Wednesday, August 20, 2014 7:12 AM

Answers

All replies

  • hi,

    while the records are added to the table, assign them to an instance first in the db (add a column 'instance');

    then have every instance send the top 1 unsent record that is assigned to them, and after they are sent, mark the record as 'sent'.


    Regards, Nico

    pdfaid, my blog

    Wednesday, August 20, 2014 7:32 AM
  • I did not get you.. I have an ID column that is unique.

    Please can you elaborate what you intend to say... with example.

    Thank you

    Wednesday, August 20, 2014 7:47 AM
  • SERIALIZABLE only means that until you commit, SQL Server guarantees that the result set will be the same if you repeat the query. It does not perform any updates or lock a row for reacing by other users.

    It sounds like you have some sort of a queue. Thus, you should look into Service Broker.

    In the meanwhile you can try:

    BEGIN TRANSACTION

    SELECT TOP(1) .... FROM SMS_table WITH (READPAST)

    -- Send your SMS here

    UPDATE tbl SET fetched = 1 WHERE ...

    COMMIT TRANSACTIOn

    Don't use SERIALIZABLE, but stick to READ COMMITTED.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 20, 2014 7:47 AM
  • To add to Erland's answer you may have to add the UPDLOCK hint too to your select query, so that the locks are held and parallel transactions will read different rows
    SELECT TOP(1) * from T1 WITH (UPDLOCK,READPAST)


    Satheesh
    My Blog | How to ask questions in technical forum



    Wednesday, August 20, 2014 7:59 AM
  • I like Satheesh Variath approach, it's way simpler than using Service Broker, and prevents more than one sesion from send the same SMS. Additionally, not SERIALIZABLE isolation level is required, just READ COMMITTED.

    EntityLite: A Lightweight, Database First, Micro ORM

    Wednesday, August 20, 2014 8:05 AM
  • To add to Erland's answer you may have to add the UPDLOCK hint too to your select query, so that the locks are held and parallel transactions will read different rows

    SELECT TOP(1) * from T1 WITH (UPDLOCK,READPAST)

    Yes, UPDLOCK is crucial here. Thank you for making this correction!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 20, 2014 2:09 PM