Hi everybody,
We seem to have an issue regarding MS SQL Transactions. The solution is used by 10-20 users and sometimes almost everybody gets its window frozen or can acces the solution and the program crashes. We noticed that each time there was at least one transaction running.
I found this post on the forum and I’m pretty sure that our problem. But I don’t know how to disable transactions (not using it).
But I don’t know how to disable transactions (not using it).
I don’t understand this part of the question. If a transaction is running somewhere, it was started somewhere in your code using databaseManager.startTransaction(). So search your code for that and “disable” it there.
And yes, your observation is correct, SQL Server uses a very pessimistic approach on transactions by simply locking a fetch. So if you load 1000 records and start a transaction because you want to edit one record in a transaction, the whole 1000 records are locked. Other clients looking at that data are “dead” as long as the transaction is alive. Only very few databases have an approach to transactions that really deserve the name, one of them is Oracle, another is PostgreSQL.
In fact, I don’t want to get rid of transactions but finding a way to keep them and stop locking the records or set some kind of timeout. Basically something which avoids all these records to be locked.
I definitely need to use transactions for rollbacks. However I can use MS SQL 2005 if it’s an MS SQL 2000 issue.
It seems to be a big problem/bug of Servoy or are there know solutions?
Your problem is: you are using the wrong database for what you want. I used to develop on MS SQL, but we never deployed it only because of this. This is absolutely not a Servoy problem.
One workaround could be to create a foundset with just the one record you are editing. Then start your transaction. I would expect that only this one record is locked then.
btw: this behaviour is said to have improved a bit in MS SQL 2005. In my tests, however, that improvement was not good enough.
and another option: you can configure SQL Server to allow so called “dirty reads”. Then the locking problem is minimized, but your data quality is in danger.
Interesting, I’ll try your solution and will search more information about these “dirty reads”.
Thx
yes, but be careful with this. It’s really dirty. The whole matter is around isolation levels. Servoy uses a READ COMMITTED mechanism. If you do a query using READ COMMITTED on MS SQL while somebody has an uncommitted transaction running, MS SQL simply locks the data until that transaction has been committed. Oracle, for example, would just give you the (last) committed version of the data. That’s how READ COMMITTED should work, but it does not on MS SQL. Dirty reads or READ UNCOMMITTED isolation level lets another user read the uncommitted data from the transaction, even if that gets rolled back later. So you can not really trust your data anymore.
I have found how to avoid it using MS SQL Server 2005: you need to enable Snapshots to your database.
Here is an interesting article: http://www.informit.com/articles/articl … 8&seqNum=1
As you have to close every connection to your database and can’t run the code of the article like that, here is the code to enable Snapshots:
if CAST(serverproperty('ProductVersion') as varchar) not like '[1-8].%'
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE dbName SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE dbName SET MULTI_USER;
'
Exec(@sql)
end
go
Source: http://bytes.com/groups/ms-sql/494590-h … d_snapshot
I have tested it with Servoy and it works perfectly!
Here is the test I have done:
When you say i save first do you mean that you saved and committed the transaction?
Because as long as you dont commit you colleague shouldnt see anything.
The other question is If you edit the field. Do you save the field to the database without doing the commit
then you colleague should open up his client (not earlier so you change, save but dont commit first) and go to that form
Can you colleague then see the data? And also open up a transaction and change and save (but dont commit) stuff?
jcompagner:
When you say i save first do you mean that you saved and committed the transaction?
Because as long as you dont commit you colleague shouldnt see anything.
Yes when I save I commit the transaction.
jcompagner:
The other question is If you edit the field. Do you save the field to the database without doing the commit
then you colleague should open up his client (not earlier so you change, save but dont commit first) and go to that form
Can you colleague then see the data? And also open up a transaction and change and save (but dont commit) stuff?
I have tried this using forms which contain sub-forms (tableviews) linked to different tables using relations. In fact I can add entries to this sub-forms by clicking on a button. And each time I click to add a new one it saves the previous one but don’t commit.
When I try with a colleague it doesn’t display the saved bits. But also once I save and commit the full form (including the sub-form), the sub-form isn’t refreshed on my colleague screen as well.
Same thing if I create a new entry using any form, save and commit it. It will not appear in the entries list of my colleague. He has to refresh manually. But we didn’t manage to refresh the sub-forms. We had to restart the Smart-Client to see the changes.
One last problem. When I add 2 new entries in the sub-form (which will get the id 1 and 2) then save and commit it. Then my colleague (logged in before the edition) go to this sub-form, sees no entry, add a new one. Then I close the Smart Client, load the sub-form again and see the list of 3 entries.
In fact lists don’t seem to be refreshed automatically like the text fields
However, now there is no user lock any more, which is the most important for me.
is the subform a relation?
jcompagner:
is the subform a relation?
Yes, it is displaying the result of the relation between the form and the subform (the list of the subform is filtered using the elements of the form which displays the details of an element)
a relation should refresh. Does it refresh when you dont use transactions?
jcompagner:
a relation should refresh. Does it refresh when you dont use transactions?
We did several tests and came up with the following conclusion: lists update/refresh does not work only in the case of the addition of a new element whether or not you use transactions or relations.
On every type of list where we have done our tests, the deletions were displayed instantly, as well as the editions of elements details. Using transactions or not didn’t change the behaviour.
can you make a case for this?
Somehow new records are not matched to your relation.
jcompagner:
can you make a case for this?Somehow new records are not matched to your relation.
I don’t use relation on all lists. There are lists which just display the result of a simple query.
now i am confused.
Are the foundset you show in the subform based on a relation or not?
In my subform yes, but it doesn’t matter. We did our tests on several different lists everywhere in the solution (not only subforms) and we got the same behaviour everywhere, with foundsets based on relation AND foundsets not based on relation.
I might have said in my previous posts that it was working fine in the other parts of my form but that wrong. There is the same problem on everything type of lists.
Do you still want me to make a case?
normal foundsets will not update there records when there is an addition
this should be done by related foundsets