pessimistic concurrency in OS

pessimistic concurrency in OS

  

Hi 


I need to update a table (sometimes it can happen to update 500 rows once). 


One user A can update the results in a pop up after choosing some rows in a table records and clicking in a button to open the pop up and there to write some stuff and save the information. 


I want to block the table in Database, when the user submits the save button, so an user B or an user C cannot update IF there is, at least, one row BEING updated/written by an user (A).  This is a pessimistic concurrency to prevent that any other user can edit at all any changes in DB while an user A is editing... I know there is a GetTableForUpdate for OS.. but dunno how to use it. A problem with the OS documentation as usual.  

So in the action that saves the info of popup (I have used SQL SERVER to update the info) I need to know how I can implement this pessimistic concurrency.

In the save action in the popup I'll use a sql block.  

In the snippet (below) @id will be retrieved by a string split (the string is a session variable to save all selected Ids in the table records and then split by this action) and using a for each loop we can save each id received in the popup. 


The next snippet I'll try to implement inside a For Each loop. (yes, iT IS fast for 1000 rows, I have tested it, but now it still lacks the locking mechanism).  I have erased some attributes in purpose.

SyntaxEditor Code Snippet

IF (SELECT atribute1
            FROM table1 a
            WHERE a.Id=@Id and year=@Year) = ''
             
            BEGIN
                INSERT INTO **
                VALUES (@Id, @Year, NULL, @C, @A, NULL, NULL,getdate(), @Username)
    
            END

            ELSE

            BEGIN
                 BEGIN TRAN
                   SELECT *
                   FROM {table1}  -- to be blocked in line with @Id
                   WITH (HOLDLOCK,ROWLOCK)
                   WHERE id in (@id)  -- it will receive only one Id

                   update table_c -- this table will update @Id is being edited
                   set  result='in edition'  
                   where id=@id  

                   UPDATE TABLE b
                   SET    atribute2=@a
                   WHERE  ID IN (*****)

                 COMMIT TRAN
                 
            END
                 


I am not really sure if this can lead to deadlock... or not... one the main problems of the pessimistic concurrency .  I am not even sure it will work as desired.

problems: 


1) How can we refresh the table records to put the new info that those Ids are being edited...looks nasty to refresh in the loop... it would be flickering all the time? I mean, I want to show that the result in the table records is IN EDITION. Is there another way? 


2) Now how can we implement a way that the user B receives a message (before the popup!) that he cannot to edit (because at least one Id is being updated)... before opening the popup it must be able to detect if any id chosen is being blocked... 


3) another strategy would be welcome :D 

Not sure I fully understand but ...

Normally one would implement a pessimistic locking strategy in a database by putting a lastUpdatedTimestamp on a row and the checking whether the lastUpdateTimestamp your user has is equal to the one that is there now, if not someone else updated it while your user had it. 

You could use this over a number of rows my using the max you have vs the max that exists or something like that as a check before trying to update, but could not prevent an update in the instant the set of rows takes to update so should still check each row as it is updated and abort the whole set which would happen in the transaction bracket on the server I beleiev (or you could open a transaction and close it yourself)

Implementing table loacking is not a good idea even if you could do it in OS which I don't see how.

Its not really a Get'Table'ForUpdate for OS its a Get'Table[Row]'ForUpdate as it needs an Id and its used exactly like a GetForUpdate in sql and hpefully there is the shortest possible time between the GetForUpdate and the Update or else chaos could ensue!

Its not really a Get'Table'ForUpdate for OS its a Get'Table[Row]'ForUpdate as it needs an Id and its used exactly like a GetForUpdate in sql and hpefully there is the shortest possible time between the GetForUpdate and the Update or else chaos could ensue!

oopsss.. not good, really. 

So are you telling that locking a table is not a good idea, only we can lock some rows in the table, right? 

as you see my sql code, it ONLY will block the lines with the selected Ids, not the whole table.

Thanks. 

In most RDBMs's it is definitely possible to lock a table but it is almost always a bad idea from an online app with multiple users as it can result in a deadlock condition which will bring the system to a halt for anyone trying to access that table.However I don't think it possible to do this with normal OS commands, you may be able to use some direct updating, a stored procedure or something but I wouldn't even try it.

The OS GetForUpdate will lock a row (and I don't even like doing that) imo implementing pessimistic locking on a table is better using the timestamp approach and aborting the tx if it has been updated in between you getting it and you trying to update it.