1. Alfredo Santibanez
  2. PowerBuilder
  3. Wednesday, 27 May 2020 21:44 PM

Hi

RE:  pb2019 and pb12.6, SQL server 2008 and SQL server 2017 have the same issue

I am trying to control two/more users opening the same record and  allowing the save just for the first one to update, and rejecting the others.

As I am using old object, my actual DBerror even used to check this, seems to be from old days of SQL Anywhere 5,0 and SQL Server 2000.

   CHOOSE CASE al_sqldbcode

   CASE -3,532
        messagebox(adw.iw_parent.title, "Record Modified/Deleted by Another User" , exclamation!)

 

I was able to review actual work using the next configuraton:

sqlca.lock="SS"  from http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc33820.1250/html/dbparm/BFCEAHIJK.htm

and tryed a recommend test from

https://www.red-gate.com/simple-talk/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/

 

setting update the database to

 
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
 

and if using embeded Sql syntax, it does not allow the second user to update, responding the correct SQL server Error 3960.

Code is simple:

user 1                          user2

connect                       connect

select                           select

update

commit

                                  update (error is reported)

                                  commit

If the commit of the user1 flow is done after the update in the user2 flow, a wait is done for user 2 until I commit in user1, but the error is reported in the update of user2

During this process, the select from tempdb of  sys.dm_tran_version_store and sys.dm_tran_active_snapshot_database_transactions and others shows both records with the same row_SEtid

 

I though "PROBLEM SOLVED", but when used with datawindow update method does not behave the same way.  What I found after a lot of debuging is:

Of course connect is the same way, select is dw_1.retrieve() and update is dw_.1update(false, false)

  • if user1 updates and commits, user 2 does not receive and error.  The dw_1.update of user2 returns 1 and dberror is not called.
  • If user1 updates, user2 udpates, wait happens, and after commit of user1, user2 receives the error.

I reviewed over and over and the transaction object is correct, lock is set to SS, datawindow object transaction object is correctly asigned, and the select from the SQL server recommended from tempdb, shows both updates with the same rowsetid too.

I also tried with simple datastores, instead of embeded Syntax to verify that no code of a big application is creating problems, same problem as with DW.

I would like to ask for some help and suggestions in this situation.

 

Best Regards

Alfredo Santibáñez

Attached testblock.pbl is version 2019 and testblock2.pbl is 12.6

 

Error is:

 

 

 

 

 

 

 

 

 

 

Attachments (2)
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 28 May 2020 14:21 PM
  2. PowerBuilder
  3. # 1
1
Votes
Undo

Hi Alfredo;

     In these cases, I just like to lock the 1st common table and its associated row in the transaction for SPID #1 and let it hold the lock for the duration of the DB transaction. For example, if you open the 1st DWO that retrieves data at the start of the business transaction, go the SQL Painter and then flip it into "Syntax Mode" you can then apply the locking request, as follows:

  SELECT "Chris"."id",   
         "Chris"."Name",   
         "Chris"."Description",   
         "Chris"."Notes"  
    FROM "Chris"  WITH  (HOLDLOCK)
   WHERE "Chris"."id" = 1    

   Now that the 1st table row is locked, SPID#2 waits. Once the business transaction on SPID#1 either completes with a COMMIT or backs out via a ROLLBACK, the lock on the 1st table is removed and SPID#2 can continue.

  For SPID#2 and these type of transactions in general, set the wait time low. Thus if something happens to SPID#1 and its delayed, SPID#2 will get a timeout error. You now need to program to trap this timeout error and inform the user to try again as the data was temporarily unavailable.

HTH

Regards ... Chris

Comment
Thank you for your answer Chris, I am reviewing your response and I would like to comment it. Sorry for then length I tested several options.



1) I read about table HINTs and found something that may be usefull for you

"HOLDLOCK is sort of deprecated, and you should use SERILIZABLE instead."

I was unable to find an end of life for it, but I found some references to it in msdn and stackoverflow that looks reliable. Also found that they are equivalent



Also want to mention that table HINTS would be a nice feature in DW painter, like the DISTINCT is used now, depending on the connected DB.



2) This give a good idea to try, because I am using a different transaction with "serializable" sqlca.lock for handling document consecutive numbers, that now I see can be made in the same transaction, looks better this way, will test it, but no the topic of this post.



The "serializable" option works perfect with embbeded SQL, DB is blocked.



3) I was trying to follow up your suggesion, but I was unable to make it fit. This is how I see it, please let me know If I am making something wrong



I would like to continue to be able to Read data from any user, that is commited, even if users have open records on screen. Optimistic way. I just tested I can read records opened with HOLDLOCK for the 2 Spids



All users will be using the same DW to open the record.



With LOCKTIME default -1 , The problem is that if I write to any of them one waits until the second one updates, I would like to have the first one to write (not the first one to open) without problems or depending on the others. The DW error for the second update error is "transaction was deadlocked...."



With execute IMMEDIATE "SET LOCK_TIMEOUT 1800"; both returns the error "lock request time out required" , no one can save. I can not reach the COMMIT in any case without receiving an error in the update. I tried several order of exectuion but no one worked.



Unfornatally this is not what I am looking for.



4) I forgot to mention in my original post that I was using in the Update properties the "where clause for update/delete" the option "Key Clause".



First, I have to be honest that I forgot about it, probably read it in PB5 and never again, and changed because I thought it is more "efficient" to send a key to the DB to make the update, than sending a big where clause with a lot of fields.



With the where clause for update in the other options "modified columns" and "updatable columnes", the update returns "error -3", record delete/modified by another user, even using "Read commited" set up.



But I just tested with trace what is sent and later checked in the "query execution plan" , the result is that update with just the key is more efficient. I cannot tell for the moment in a multiuser enviroment, multiples updates, etc. if this will affect the response times.



But in any case, if the DB is set to "snapshot isolation" and the embeded SQL respects this set up, why the DW does not?



Thanks again for your help

Regards

Alfredo







































  1. Alfredo Santibanez
  2. Friday, 29 May 2020 01:40 AM
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.