Reply to: Should DBA know to check theres no DB connections before performing a data update

[Old Hack]

I love the abuse levelled before you actually know what the app is.

It's an old VBA GIS application linking to an Oracle Spatial DB holding maps. The data gets locked by the application and if the users haven't logged out, it stays locked and cannot be updated. I guess I should have been more specific but it was a vent, nothing more...

Oh, and for all the sledging, you obviously realise that having more than one person at a time edit spatial data is a big no no...

[eek]

Your architectures dont use optimistic locking then? Are you not using versioning on your rows? Which you usually should be in scalable multiuser systems.

The DBA update would incr the version number. The cache would not be able to overwrite that later. You should handle optimistic locking version error in your app-reload the latest version and maybe try again. This is how I've used ORMs such as Hibernate in the past. It's a standard approach.

Bouncing the DB should be an absolute last resort due to needing to refill caches in the middle tier.

Blooming heck something from aussielong I can 100% agree with

[aussielong]

If you are using ORM which mostly everyone does these days in writing java apps ( I would think .NET has eventually got there as well) and using third level caching which you should be then you have to drop the appication or else what you are updating might be written over by the application cache.

It is pretty standard tech and a pretty standard way of using it.

As I said it depends on the use of the DB but it is normal ( necessary )to drop the connections on the architectures I use.

Your architectures dont use optimistic locking then? Are you not using versioning on your rows? Which you usually should be in scalable multiuser systems.

The DBA update would incr the version number. The cache would not be able to overwrite that later. You should handle optimistic locking version error in your app-reload the latest version and maybe try again. This is how I've used ORMs such as Hibernate in the past. It's a standard approach.

Bouncing the DB should be an absolute last resort due to needing to refill caches in the middle tier.

[minestrone]

ORMs are terribly misunderstood by most people that use them, fantastic at delivering clean code quickly written to do page sized data loading but I keep finding people writing batch jobs with it. Battering the shit: out of the DB for minutes on end to do something a stored proc could do in a second.

[eek]

If you are using ORM which mostly everyone does these days in writing java apps ( I would think .NET has eventually got there as well) and using third level caching which you should be then you have to drop the appication or else what you are updating might be written over by the application cache.

It is pretty standard tech and a pretty standard way of using it.

As I said it depends on the use of the DB but it is normal ( necessary )to drop the connections on the architectures I use.

Which is why ORM's are more pain then they are worth. The person who retrofitted one at current client co deserves to be shot.

[minestrone]

Sounds like the application architecture needs a rethink.

If you are using ORM which mostly everyone does these days in writing java apps ( I would think .NET has eventually got there as well) and using third level caching which you should be then you have to drop the appication or else what you are updating might be written over by the application cache.

It is pretty standard tech and a pretty standard way of using it.

As I said it depends on the use of the DB but it is normal ( necessary )to drop the connections on the architectures I use.

[centurian]

Sorry OP but I'm approaching 10 years as a DBA/developer, mostly direct for Oracle, and have never once had a scenario where people have had to log out for a 'data update'.

WHS - same here. Never had to get users to log out for a data update.

Now I have had a few applications where this might have been an issue, so the application / DB schema accomodated this, by being able to pre-load the data gradually, but in an inactive state - and then made active when ready.

Sounds like the application architecture needs a rethink.

[Kanye]

Sorry OP but I'm approaching 10 years as a DBA/developer, mostly direct for Oracle, and have never once had a scenario where people have had to log out for a 'data update'.

Occasionally some DDL or huge table level updates are scheduled for outside of business hours, but even then, leaving the occasional session connected should not be the end of the world.

Sounds like a flaw in the application locking or change processes rather than with the DBA.

[minestrone]

Why?

Cached data in some instances.

[suityou01]

It depends on what is using the DB.

Why?

[minestrone]

It depends on what is using the DB.

[escapeUK]

Personally, I code all my apps to check a database table every 90 seconds which has an entry for each program name and a true or false. If true the program gracefully closes, and then refuses to start up until its back to false.

This way I can close systems down quickly, safely and no one "forgets" and goes for lunch with their screen locked and the program still running. Before I started doing this it was easy to spend 30 mins ringing around, then walking around logging people out of apps. Often after 20 mins the people who had already come out would think it was done and go back in.

But as others have said with SQL you can do an awful lot with people still using the database.

[suityou01]

Well I had assumed the down was planned and emails sent, and that any checks would be cursory, but he didn't kick anyone off, just did the update.



Like the last bit...

You're clearly not important enough to be asking these questions and you chose to vent on here. In future, if you have these concerns why not raise them with someone important enough? If they are as incompetent as you point out, seize the opportunity, step up and sort it while alerting the powers that be rather than pissing and moaning on here. If you do, you could also negotiate a rate rise.

All the time know that you had to take this advice from Suity

[suityou01]

Sorry, this is an application which serves map data; the only thing they can update is the map data, and it cannot be updated whilst users are on it. That's a known part of the system.

Jesus, no wonder everyone thinks you're a ****.

Try and pull yourself out of your unimportant little life for a second and reconsider the question more generally.

HTH BISDI

[KentPhilip]

If it is a SQL Server (Microsoft) database then the only time you need to kick out all the users is if you are restoring a database from a backup. With one exception in all other situations you can make any changes you like, including dropping and re-creating tables, and users logged on won't affect that.

The one exception is if logged on users have open transactions holding locks on database objects - that will prevent changes to anything thus locked. But it is bad application design for a client to hold locks for an extended period of time.