-
Alfredo Santibanez
- PowerBuilder
- Wednesday, 27 May 2020 09:44 PM UTC
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:
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.