do select * lock tables on oracle 10 and 11
« on: December 01, 2017, 06:14:01 PM »
Hi everyone, here with a friend are discussing about oracle locks on databases and how to resolve that problem. I have been talking about for a lot of days, and thinking about data trees architecture and why locks happen. We have tried some solutions and the only one we see working better is putting the columns name on the query. That's very curious, because I undestarnd about * is just a "fast track" to don't put the columns name.

I was thinking on the difference is because when you put a "*" on the query, the server have to go to master tables to recover the columns name and data types. But, I still thinking if this is the correct response or not.


Somebody have any iddea about what is the difference?

*

Offline Pete Svarrior

  • e
  • Planar Moderator
  • *****
  • Posts: 15534
  • (◕˽ ◕ ✿)
    • View Profile
Re: do select * lock tables on oracle 10 and 11
« Reply #1 on: December 02, 2017, 10:58:05 AM »
Your question isn't particularly clear. Any SELECT statement will result in a transitional lock - anything else would break ACID. This is not a "problem", but an essential feature of any DBMS.

The wildcard (*) is very far from a "fast-track to not put the columns name". If anything, it's the opposite of a fast-track.

Without knowing what exactly you're doing or why, it sounds to me like you're opting for oversimplifying your queries for personal convenience, and then complaining about the performance implications of doing that. If that is correct, I'd recommend you take a step back and either learn more about how a DBMS works, or ask a more experienced colleague for help.
Read the FAQ before asking your question - chances are we already addressed it.
Follow the Flat Earth Society on Twitter and Facebook!

P.S.  All of us illiterate folks understood this the first time.

Re: do select * lock tables on oracle 10 and 11
« Reply #2 on: December 04, 2017, 08:47:36 PM »
The wildcard (*) is very far from a "fast-track to not put the columns name". If anything, it's the opposite of a fast-track.

What I'm looking for, is to demostrate the select * is a wrong solution on querys. I'm trying to generate an advance documentation to discuss with some coworkers who said this is wrong.

*

Offline Pete Svarrior

  • e
  • Planar Moderator
  • *****
  • Posts: 15534
  • (◕˽ ◕ ✿)
    • View Profile
Re: do select * lock tables on oracle 10 and 11
« Reply #3 on: December 05, 2017, 11:54:29 AM »
What I'm looking for, is to demostrate the select * is a wrong solution on querys.
It's not a "wrong solution on queries". It's far too broad a tool to be "right" or "wrong" for a vaguely defined use case.

I'm trying to generate an advance documentation to discuss with some coworkers who said this is wrong.
You really shouldn't be writing documentation.
Read the FAQ before asking your question - chances are we already addressed it.
Follow the Flat Earth Society on Twitter and Facebook!

P.S.  All of us illiterate folks understood this the first time.