Post reply

Row level lock

  • April 9, 2008 at 10:29 pm

    #184347

    Hello

    I have one scenario for that i don't know how to fulfill.

    Let take example. User-A is reading row now at the same time if user B is trying to read same row than he must wait until User A is update(Update selected row) row.

    It is required because we have one table which generates company wise invoice number. Now if User belongs to Company "A" is going to generate Invoice than any other user belongs to same company cannot generate invoice until first user save invoice. It is required because we cannot allow number gap into invoice number. It is possible that first user can cancel invoice generation process any time.

    Let take another example If User-A is going to generate invoice, now invoice number '9999' is generated for him but same time User B is going to generate invoice than he must wait until User A done with his work because it is possible that User A cancel invoice generation process. If User-A cancel invoice generation process than User-B must get the invoice number '9999'.

    Thanks

  • April 9, 2008 at 11:34 pm

    #800765

    [font="Verdana"]

    patel mayur m (4/9/2008)

    Hello

    I have one scenario for that i don't know how to fulfill.

    Let take example. User-A is reading row now at the same time if user B is trying to read same row than he must wait until User A is update(Update selected row) row.

    It is required because we have one table which generates company wise invoice number. Now if User belongs to Company "A" is going to generate Invoice than any other user belongs to same company cannot generate invoice until first user save invoice. It is required because we cannot allow number gap into invoice number. It is possible that first user can cancel invoice generation process any time.

    Let take another example If User-A is going to generate invoice, now invoice number '9999' is generated for him but same time User B is going to generate invoice than he must wait until User A done with his work because it is possible that User A cancel invoice generation process. If User-A cancel invoice generation process than User-B must get the invoice number '9999'.

    Thanks

    Mayur, if these are the scenarios then this system should be single user based not multi-user, as user have to wait until the other user(s) finishes their task. Let’s say if User_A started punching invoice and he got the system generated invoice number 100 for 1st invoice, by the time User_B have to wait until User_A completes and saves his invoice. When User_A finishes, User_B got system generated invoice number 101 for 2nd invoice. Vice versa User_A have to wait for User_B for punching next invoice. Then what about the User_C, …, User_Z?

    I think once any user generates invoice number for punching invoice, this number should be locked and system should generate the next number for allowing other user to punch the invoice. If incase user cancels his invoice in between such number should be allocated to the other user who is trying to generate the invoice number. Means before generating any invoice number, system should track those invoice numbers which are canceled in between by others. I can understand, this is crucial part to implement, but this is the way I would have been implemented in such situation.

    Hopes, I have explained well. Let me know the doubts, if any.

    Mahesh

    [/font]

    MH-09-AM-8694

  • April 10, 2008 at 12:25 am

    #800776

    Hello Mahesh

    Thanks for a prompt reply

    But the problem is that we have more than one user. Invoice saving process will not take more than 3 to 4 seconds. We cannot give unused number to invoice it must into sequence. Example .If User-A got 9999 invoice number and now if we allow User-B to generate number than he got 10000 if he save invoice than invoice date is 10.04.2008, but if User-A cancel invoice process than 9999 stay unused number. Now after 10 days any user generate invoice number and save than he got unused number 9999 and invoice date is 20.04.2008 which is logical wrong as invoice number 10000 is dated as 10.04.2008 and invoice number 9999 is dated as 20.04.2008.

  • April 10, 2008 at 12:43 am

    #800780

    [font="Verdana"]

    patel mayur m (4/10/2008)

    Hello Mahesh

    Thanks for a prompt reply

    But the problem is that we have more than one user. Invoice saving process will not take more than 3 to 4 seconds. We cannot give unused number to invoice it must into sequence. Example .If User-A got 9999 invoice number and now if we allow User-B to generate number than he got 10000 if he save invoice than invoice date is 10.04.2008, but if User-A cancel invoice process than 9999 stay unused number. Now after 10 days any user generate invoice number and save than he got unused number 9999 and invoice date is 20.04.2008 which is logical wrong as invoice number 10000 is dated as 10.04.2008 and invoice number 9999 is dated as 20.04.2008.

    Ya Mayur, thats why I have already mentioned in my prev post that you should add one more step at generating invoice number. system should track those numbers which are unused (generated and had been canceled in between) and immediately use them. In a brief, add one step where you generate invoice number where you have to check the unused/canceld numbers, so that system can immediately use them up.

    any doubts?

    Mahesh

    [/font]

    MH-09-AM-8694

  • April 10, 2008 at 1:52 am

    #800795

    Hi mahesh

    Thanks

    I cannot allow single number gap.

    I have to find out some other solution for it.

    Thanks for your help

  • April 10, 2008 at 3:02 am

    #800827

    Try using a transaction and UPDLOCK.

    Here is an outline - you will need to add error handling etc.

    DECLARE @NextInvoiceNumber int

    BEGIN TRANSACTION

    SET @NextInvoiceNumber =

    (

    &nbsp&nbsp&nbsp&nbspSELECT NextInvoiceNumber

    &nbsp&nbsp&nbsp&nbspFROM InvoiceNumbers WITH (UPDLOCK)

    &nbsp&nbsp&nbsp&nbspWHERE Company = 'A'

    )

    UPDATE InvoiceNumbers

    SET NextInvoiceNumber = NextInvoiceNumber + 1

    WHERE Company = 'A'

    INSERT INTO Invoices

    SELECT @NextInvoiceNumber, ...

    COMMIT

  • April 10, 2008 at 4:03 am

    #800854

    Hello Ken

    Thanks for you reply.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply