Connection.setAutoCommit and Database locks
Hello friends,
I would like to discuss using transactions through Connection.setAutoCommit function. My question is - what type of lock does the
Connection.setAutoCommit(false) create?
Is it a database lock, or a lock based on the queries that follow? For example, using Microsofts ADODB.Connection object, and calling BeginTrans function will lock the whole database, making it unaccessible until a CommitTrans or RollbackTrans is called.
Is java Connection object the same?
[515 byte] By [
seralex] at [2007-9-26 2:37:13]
Java Connection manages lock based on queries.
Connection.setTransactionIsolation() set the isolation level.
If you choose the strongest one, TRANSACTION_SERIALIZABLE, you prevent other connections from any change concerning your query result, including insert of rows which you would get if you repeat the query.
JDBC - as I see it - is a tool for a very secure and elegant way of "using" a database, but not for its administration. It takes only so much influence on the data as needed to do a user's task - this is just like every user of a database should behave.
DB Administration has its place on the server, and there surely any tools from the DBMS vendor are available to do this, optimized to the specific best ways just for this DBMS.
So there's really no need to have this in JDBC.
can you confirm this! I find it hard to believe the ENTIRE database is locked, not even restricted to a session?!
>For example, using Microsofts
> ADODB.Connection object, and calling BeginTrans
> function will lock the whole database, making it
> unaccessible until a CommitTrans or RollbackTrans is
> called.
mchan0 at 2007-6-29 10:06:10 >
Yes, I can confirm it.
For example take a classic case of using Microsoft technology: VB application + ADODB + MS SQL Server.
if you connect to sql server on a (remote) network to do more or less significant update, affecting a big portion of one of the tables, calling Connection.BeginTrans will lock the whole database.
Since Connection represents a database, ADO locks it when transaction is started.
Thanks, Hartmut,It's good to know java Connection creates locks based on queries.
> Yes, I can confirm it.
> For example take a classic case of using Microsoft
> technology: VB application + ADODB + MS SQL Server.
> if you connect to sql server on a (remote) network to
> do more or less significant update, affecting a big
> portion of one of the tables, calling
> Connection.BeginTrans will lock the whole database.
> Since Connection represents a database, ADO locks it
> when transaction is started.
I am also astonished, but I would believe it.
It is a strange,but obviously very common behaviour, to retrieve and lock everything you may ever need, and then process up and down your resultset, without any plan, updating here, deleting there, and the entire time the driver is communicating with the database, as if a database is for no other sense than just beeing a toy of a confused user!
I suspect, this (total false) kind of using a database was invented by MS to make their Access to a "standard" of what people who don't know it better think what a database should be: a one user playing field.
JDBC has some similiar features (I think one didn't want to stay behind), from those just the Java and db stuff beginners are very attracted.
A connection represents a database - what a nonsense!
One car represents the motorway - so anybody else stay out, because now I'm coming!
This is the MS behaviour on market ;-)
But sad to see to what high degree they have succeeded in foolishing the entire business and even more home user's world! :-!
Ok, I might have used wrong words saying that the Connection represents a database, but it doens't change the essense - it locks the whole database, which does seem rediculous.
> Ok, I might have used wrong words saying that the
> Connection represents a database, but it doens't
> change the essense - it locks the whole database,
> which does seem rediculous.
>
As ridiculous as:
* each user must have an own computer, for the best a server.
* each user is administrator of this computer. He gets a lot of buttons, so he can do everything, without understanding anything.
* each computer can be used by only one user at the same time.
* if one program has to do some work, it is not able to repaint or even minimize its window, so the whole desktop is blocking.
* to type one word in the most common text editor needs for a short time all system resources.
* every work on a computer makes a document, which each user can change, copy, delete, send to others - and all of this with an unlimited count of "undo".
* each program has its own file format. Nearly each new version changes the format, so you must upgrade to be not left behind.
On this background its a very integrative idea that also a database should belong to one current user only.
(We could continue: also the business should belong to one company only. All the billions of $ should belong to one person only. ...)
Really - this is a total different point of view. It is nothing I could call ridiculous. I think, it's a terrible risk and harm that people use all of this without realizing this difference.
Well Said Harmurt,Since the duration between my read and update are long - i am not going for any isolation or transaction sort of.... Rather, i would like to take care of thru' program logic.
No program logic can save you from someone switching power off your server.
He was referring to another topic, I think: http://forum.java.sun.com/thread.jsp?forum=48&thread=155014
