Dirty reads

Questions and answers regarding general SQL and backend databases

Dirty reads

Postby martinh » Wed Oct 15, 2008 2:07 pm

Hello,

I noticed a situation where a Servoy client was waiting because some other user was changing a record that the client also wanted to read.

I want to avoid this, so I want to do dirty reads.

Since this looks to me as a database problem, I found in my case, that my database connection needs an extra line.

I am using SQL Server 2005, so I want to add the following line:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

But there is no way from Servoy to give extra statement when creating a database connection.
I even don't know if a db connection stays alive as long as the client has connection.
Or even the DB-connection is handled by the Application Server and not by Smart Client.
In that case, how many db connection will there be?

I was thinking about sending a RawSQL statement on open of my solution, but will that really work or is there another way to do that?

Martin
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Dirty reads

Postby Hans Nieuwenhuis » Wed Oct 15, 2008 2:11 pm

Hi,

Can you not do that in an "onLogon" stored procedure in the database ?

That is the way i do certain settings in an Oracle database. (like case insensitive sorting)

I am not a sqlServer specialist, but i gues that Sqlserver also has stored procedures.

Regards,
Hans Nieuwenhuis
Betagraphics
http://www.deltics.nl
http://www.betagraphics.nl

Servoy Version 7.3.1
Java version 1.7.0.x
Database Oracle 11g
User avatar
Hans Nieuwenhuis
 
Posts: 1026
Joined: Thu Apr 12, 2007 12:36 pm
Location: Hengelo, The Netherlands

Re: Dirty reads

Postby martinh » Wed Oct 15, 2008 2:19 pm

Hi Hans,

Thanks for the reply.
If I can execute a SP, then I can also give the SET statement; both can be done using rawSQL
Problem is that as far as I know this SET statement only lives during a connection.
And I don't know how Servoy handles db connections.

Martin
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Dirty reads

Postby patrick » Wed Oct 15, 2008 2:27 pm

Other question: are you sure you want to do this? Your data integrity is in danger if you do that kind of thing. The pessimistic locking of SQL Server is one of the reasons we dropped that DB and switched to Oracle.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Dirty reads

Postby martinh » Wed Oct 15, 2008 2:31 pm

Yes I do, because when a user wants to change a record, he must first go to editmode and a lock is placed.
And when a record is already locked, then a message is given, that record is locked by another user.

But now I have a user waiting for 45 seconds because he selected a record for just viewing, that was locked by another user.
And I dont want a user to wait for 45 seconds, because he will call our helpdesk.
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Dirty reads

Postby Hans Nieuwenhuis » Wed Oct 15, 2008 4:38 pm

Hi,

If you do it by an (onLogon) SP then this will be executed every time a connection is made.
So it will even work if Servoys drops the connection and reconnects.

Regards,
Hans Nieuwenhuis
Betagraphics
http://www.deltics.nl
http://www.betagraphics.nl

Servoy Version 7.3.1
Java version 1.7.0.x
Database Oracle 11g
User avatar
Hans Nieuwenhuis
 
Posts: 1026
Joined: Thu Apr 12, 2007 12:36 pm
Location: Hengelo, The Netherlands

Re: Dirty reads

Postby hansayton » Fri Oct 17, 2008 9:15 am

Hi,

I am really, really looking for a way to set the isolation-level for for all of the connection-pool the application-server runs, either directly from the admin-page, from within the config-file, or by way of passing it from within the solution / client to the application-server.
Any (working) properties that can be set in the config file that I am not aware of ?

Regards, Hans Nieuwenhuijs
Hans Nieuwenhuijs
Ayton bv
Netherlands
hansayton
 
Posts: 11
Joined: Fri Nov 09, 2007 11:03 am

Re: Dirty reads

Postby martinh » Fri Oct 17, 2008 9:59 am

Hans Nieuwenhuijs

and

Hans Nieuwenhuis

are replying to this topic.

Don't get confused , because I was :D

hansayton wrote:I am really, really looking for a way to set the isolation-level for for all of the connection-pool the application-server runs, either directly from the admin-page, from within the config-file, or by way of passing it from within the solution / client to the application-server.


I think some SQL Config file would be a good idea. All statements in the config file must be executed each time when a database connection is created.
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Dirty reads

Postby rgansevles » Mon Oct 20, 2008 2:17 pm

We can add an extra field to the server definition for a statement(s) to be executed when a new connection is made to the database.
This way you can configure session settings in servoy without needing to change the database.

Please file a feature request so we can work on this.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Dirty reads

Postby martinh » Mon Oct 20, 2008 3:03 pm

rgansevles wrote:We can add an extra field to the server definition for a statement(s) to be executed when a new connection is made to the database.
This way you can configure session settings in servoy without needing to change the database.

Please file a feature request so we can work on this.

Rob


I created feature request 167169
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Dirty reads

Postby hansayton » Tue Oct 21, 2008 9:22 am

Hi,
I strongly second that feature-request.
Would like to see it added, not only on 4.x, but also in 3.5 if at all possible.....
Many thanxs, Hans Nieuwenhuijs
Hans Nieuwenhuijs
Ayton bv
Netherlands
hansayton
 
Posts: 11
Joined: Fri Nov 09, 2007 11:03 am

Re: Dirty reads

Postby sbutler » Wed Jul 22, 2020 11:10 pm

Old thread, but does anyone know if this feature request was ever implemented?
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Dirty reads

Postby mboegem » Thu Jul 23, 2020 12:34 am

goldcougar wrote:Old thread, but does anyone know if this feature request was ever implemented?

Not that I'm aware of

But in the latest versions, Servoy is aware of SP's.
Haven't used it yet, but I guess you can execute those, would that be a solution?

[edit] or use a real database :wink:
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1742
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Dirty reads

Postby sbutler » Thu Jul 23, 2020 4:07 am

Inherited an app that was written against Progress DB and need to alter the transaction isolation level whenever a connection is made.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Dirty reads

Postby jcompagner » Thu Jul 23, 2020 9:27 am

Progress explains here 3 options:

https://knowledgebase.progress.com/arti ... cle/P12158

but all 3 are just as horrible... we miss a 4th option: Read the Committed data (even if there is already uncommitted for that row, but for another connection that should be not even seen)

Read uncommitted data is just very bad, why are we then using transactions? If everybody already sees all the not committed data???
For servoy this is very bad because of the caches, so you have no idea if you are actually seeing data that is in the database, or that that data is already rollbacked, so you can trust your data.
So letting Servoy reading uncommitted data is a very bad idea.

This behavior is the same as we had in Sybase or Microsoft. But for Microsoft we have now: SET READ_COMMITTED_SNAPSHOT ON (https://docs.microsoft.com/en-us/dotnet ... sql-server)
Which should be on for a Servoy used SqlServer!

But for a database that locks the row at the moment there is uncommitted data there is only 1 real thing todo: DON'T use LONG transactions.
Do saves/updates in a very short window
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8828
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet


Return to SQL Databases

Who is online

Users browsing this forum: Majestic-12 [Bot] and 9 guests