OraFAQ Forum: General » Dirty reads (splitted from "NO LOCK?")

Home » Other » General » Dirty reads (splitted from "NO LOCK?")

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dirty reads (splitted from "NO LOCK?") [message #571723]

Thu, 29 November 2012 10:15

nimdil
Messages: 6
Registered: November 2012

Junior Member

While it's true in Oracle conflict between reads and writes are not possible, it's not true that NOLOCK/READ UNCOMMITTED is not needed. What if someone needs to check the content of the table at the moment with all their dirty records? versioning-based READ COMMITTED is not perfect and READ UNCOMMITTED had it's uses. It's a real shame they are completely removed.

Report message to a moderator

Re: NOLOCK? [message #571726 is a reply to message #571723]

Thu, 29 November 2012 11:24

Michel Cadot
Messages: 68730
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

What if someone needs to check the content of the table at the moment with all their dirty records?

This is a silly requirement. Who needs this? Tell us a REAL requirement or need for this!
Who needs a result that is wrong and in fact never exists?

Quote:

READ UNCOMMITTED had it's uses.

Which ones?

Regards
Michel

Report message to a moderator

Re: NOLOCK? [message #571731 is a reply to message #571726]

Thu, 29 November 2012 11:49

nimdil
Messages: 6
Registered: November 2012

Junior Member

Just because you can't think of use doesn't mean there are non.

Real life scenario:

I have rather complex insert into ... select ... statement that uses several tables and multiple functions to process data and insert them into target table. I know how many records need to be entered into target table as this can be calculated or at least estimated. However after 8 hours of running I would like to know how many records where actually processed in the system.
How?

go.

Report message to a moderator

Re: NOLOCK? [message #571733 is a reply to message #571731]

Thu, 29 November 2012 11:54

Michel Cadot
Messages: 68730
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And how is this different if you know or not for the business?
This is NOT a business need.
If you REALLY need this for your business then add a trigger on INSERT (for instance) but I doubt this is worth the cost.

Regards
Michel

[Updated on: Thu, 29 November 2012 11:55]

Report message to a moderator

Re: NOLOCK? [message #571734 is a reply to message #571733]

Thu, 29 November 2012 11:58

nimdil
Messages: 6
Registered: November 2012

Junior Member

What kind of approach is that - "business". We are talking about database and what we can and can't do. Surely this kind of information is useful because if after 8hours we have 10% of job done, we can make a decision whether we are waiting another 72 hours or not. And if we discover that we are under <1% we know that either we screwed the query up or that we overestimated our server's horsepower.

And how a trigger will work here? o_O This whole data is put into table in one query and I want how well the transformation is going on in he middle of it.

Report message to a moderator

Re: NOLOCK? [message #571737 is a reply to message #571734]

Thu, 29 November 2012 12:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How?
Code should have previously been tested & benchmarked prior to deployed to Production
Alternatively use DBMS_LOGMNR to count the records.

Report message to a moderator

Re: NOLOCK? [message #571738 is a reply to message #571737]

Thu, 29 November 2012 12:13

nimdil
Messages: 6
Registered: November 2012

Junior Member

Sometimes you just run operations once so there's no need to test/benchmark prior to deployment as there's no deployment phase.

I'm not familiar with DBMS_LOGMNR - I'm really coming from different databases, mostly and sadly SQL Server - so I don't know how this particular will work and if it also allow me to check the data or only check the count?

Report message to a moderator

Re: NOLOCK? [message #571740 is a reply to message #571738]

Thu, 29 November 2012 12:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Sometimes you just run operations once so there's no need to test/benchmark prior to deployment as there's no deployment phase.
so much for QA!

Ready, Fire, AIM!

Report message to a moderator

Re: NOLOCK? [message #571743 is a reply to message #571740]

Thu, 29 November 2012 12:21

nimdil
Messages: 6
Registered: November 2012

Junior Member

OK I don't know how you do that in your company but when I have an application in mine and I set up new application and needed to load historical data from flat files and transform it into required format, that's what happened. I run everything and QA only checked things afterward however the essential part is that historical data needed to be load only once. The daily updates processes were properly tested however as was the rest of the solution.

Report message to a moderator

Re: NOLOCK? [message #571744 is a reply to message #571743]

Thu, 29 November 2012 12:42

Michel Cadot
Messages: 68730
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ You should know how much time will run a process if you did the performances test before. I know how long will last each batch of my client because each batch has its performances sheet.
2/ In trigger you can record in another table, in a file, send an email, do whatever that can be seen outside the INSERT itself (not a good practice and performances test is a far better way)
3/ You can also (and it is less invasive than a trigger) see the amount of rollback space used by your transaction and so if you know how much space is requires for 1000 rows then you can estimate the current number of rows inserted and so estimate the rest of the time.
4/ Business is the only thing that is important, database exists only for business, so what uselessly (in the eyes of the clients) impacts the business must be avoided.

Regards
Michel

Report message to a moderator

Re: NOLOCK? [message #571746 is a reply to message #571744]

Thu, 29 November 2012 13:59

nimdil
Messages: 6
Registered: November 2012

Junior Member

4. I see your point and while I can't agree with you completely I don't think it's relevant to discuss it. In corporate environment it generally is true.
3. Interesting idea - I like it.
2. Indeed trigger can do that but performance wise it does not seem to be wise.
1. Again interesting idea. In the particular situation I have in mind I don't think it will work - it was generally one long but one-time adhoc query. I could probably do some kind of performance test however queries don't necessarily scale lineary with input volume unless there is some technique or approach I don't know about. But ussually true.

Report message to a moderator

Re: NOLOCK? [message #572232 is a reply to message #571726]

Fri, 07 December 2012 14:24

renriquez
Messages: 2
Registered: December 2012

Junior Member

Hi

so, what would be the design to solve the problem where you need to know if a transaction is in place, but you don't have rights on the Oracle database to query v$transaction??? I mean, we don't need to know what kind of work is being done (update, delete), nor what rows are being worked on, but only need to know that the work is being executed, and I need to take care of the scenario where my front-end may be shutdown

Scenario??? In a web application, we start a background task when the user clicks a button. In the background task, we start a transaction. The user can then go home, and be back hours later to access the page. If the transaction has finished, the button must be available again. I first thought to put a flag in a table, and then, through dirty read, get the flag value each time the user accessed the page. Should I carry the flag over to a file (static var is discarded)??? How have you solved this kind of situation???

Thanks in advance

Report message to a moderator

Re: NOLOCK? [message #572233 is a reply to message #572232]

Fri, 07 December 2012 14:49

Michel Cadot
Messages: 68730
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't see where is the problem, you deactivate the button, call the database (actually the application server that calls the database) and when the call completes you reactivates the button.
It is basic Ajax roundtrip.

If you want to do it asynchroneously (like an order in any seller site), then you just have to register the beginning of the (functional not database) transaction in a table, commit, start the functional transaction which will end resetting the flag in the transaction table and commit.
If the user comes back to the same page you check the transaction table to know if a transaction is going on or not.

In both cases you do not need dirty read.
You NEVER need dirty read for business purpose (or if you prefer for an end user).

Regards
Michel

Report message to a moderator

Re: NOLOCK? [message #572234 is a reply to message #572233]

Fri, 07 December 2012 17:25

renriquez
Messages: 2
Registered: December 2012

Junior Member

Hi Michel

Ajax is not a problem, nor it is to disable the button. Let me give you some more details:

The query is going to take a few minutes (may be hours, it all depends on how the client is going to have the sql implemented). Once the process has started, nobody else can start it again, until it finishes.

Should we register the beginning of the transaction and then commit, the risk exists that an error causes the app to crash while the transaction is running. The next time someone gets to the page, we would check if transaction is started, which would be "Yes", cause the registration was not part of the transaction and, since app crashed, it could not reset that flag.

With dirty read, the flag would be set as part of the transaction, but since we could read it, no matter if the app crashes: if the transaction complete or fails, the flag would be there for us to read.

I don't argue if dirty reads are good or bad, or if the Oracle's design is the better or it isn't. What I want to know, is how do you handle this scenario. What the alternative is??? how to design for this flow???

Thanks for your response

Report message to a moderator

Re: NOLOCK? [message #572238 is a reply to message #572234]

Sat, 08 December 2012 01:25

Michel Cadot
Messages: 68730
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Should we register the beginning of the transaction and then commit, the risk exists that an error causes the app to crash while the transaction is running. The next time someone gets to the page, we would check if transaction is started, which would be "Yes", cause the registration was not part of the transaction and, since app crashed, it could not reset that flag.

Yes and? It all depends on how you design your application; we can't talk there in the void without something real, an actual spepcification, I don't see anything here that requires dirty read. In a relational database, everything has a primary key, above all a business transaction, lock the key during it.
With dirty reads you are trying to workaround a bad design or implementation using a tachnical dodge.

Note: I will split this part about dirty reads from the original NO LOCK question as it has nothing to do with it.
The new topic will be called: "dirty reads"

Regards
Michel

Report message to a moderator

Previous Topic:	Latest Oracle Version
Next Topic:	UNDO tablespace is full

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 14 00:36:46 CST 2025