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:
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