problem with deadlock
I'm working on a series of servlets that allow manipulation of a database, and I need to be able to prevent deadlock or hanging if data is being changed by someone besides the user.
If this is done within the program, I can prevent it by committing the changes soon after a change, not allowing uncommitted changes to exist and keeping the database free from changes, however, if somone were to manually open the database on the server side, I'd want the client to be unable to write any new data.
I attempted to set the transaction isolation level to serializable to prevent it from doing anything, but the client side will just hang if I say, try to delete the same data entry from sqlplus, and then the servlet.
I've read up on the stuff, and this should be a simple matter to limiting the access rights of the client program, but I'm not sure how to proceed since setting the transaction isolation level didn't work;
Here's the method I use to set the transation level, which seems to happen fine, but a simultaneous delete causes the servlet to hang (which I just fix by doing a rollback on the server side, but I can't rely on this in actual implementation).
I've looked around quite a bit and read the documentation, but the solution to this problem seems to be evading me, any help would be greatly appreciated.
publicvoid setIso(String lvl)
{
try
{
st = con.prepareStatement("SET TRANSACTION ISOLATION LEVEL " + lvl);
res = st.executeQuery();
C12_Log.write(0,"SQL: SET TRANSACTION LEVEL " + lvl);
}
catch (SQLException s)
{
C12_Log.write(1, s.toString() + s.getErrorCode() + s.getSQLState());
}
finally
{
try
{
st.close();
}
catch (Exception e)
{
C12_Log.write(1, e.toString() + e.getMessage());
}
}
}
# 1
I think you need to read up on serialization. Deadlock can't be fixed by raising the isolation levels - quite the reverse.Your mechanism for changing the isolation level will work, but there's a setTransactionIsolation method on the Connection that's the normal way to do this.
# 2
The Isolation level shold n't problem solving a deadlock problem .
There are lot of problem. Deadlock occur.
1.open and proper close the all DB connections
2. Jdbc-odbc driver doesn't support when multi user access the DB
can u explain how u Db open and close connection and jdbc driver ?
Thanks
# 3
Failure to close connections causes a block on the pool, not a deadlock.Use of the JdbcOdbc driver doesn't cause deadlock.D.
# 4
Sorry if I'm being dumb, but to clarify, my way of setting the transaction level is what will work, although it is nonstandard, but it won't work in terms of fixing the deadlock problem.
I'll go and read up on serialization in the meantime, but I've searched around quite a bit, and have no real access to reference books, so I'm at a bit of a standstill. Is there a way for my servlet to figure out is something else is accessing the table it's trying to manipulate, and kill the attempt to update/delete/etc.? (In particular if there have been uncomitted changes made)
I had assumed that setting the servlet to have a serializable transaction isolation level would allow the system to detect concurrent transactions (although this would call rollback of that concurrent transaction, not a simply cease of the transaction that was trying to happen).
Other things I've read into, but don't quite understand are:
Reading the cache (dunno if I want to get into this as I don't entirely understand the concept yet)
Locking the database (not sure if this'll work, in case the external application starts to access before mine)
Commit/Rollback before my transaction (this is dodging the issue of detecting concurrent transactions)
if you could tell me what would be the ideal way to progress, or direct me to a tutorial of some sort, I'd be very grateful.
edit - nm about the commit/rollback, this would not notify the external application about the commit, would SQLEndTran be what I'm looking for to stop the change in my application?
Message was edited by:
lance_dragons
# 5
btw, right now I'm reading through
http://msdn2.microsoft.com/en-us/library/ms189132(SQL.90).aspx
and about concurrency controls. It looks as if I want to implement optimistic concurrency control (where the reference tells me to look into Isolation Levels).
However, I still don't know how to correctly implement this, I add the line
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
to my connection method, but this doesn't seem to do much in the way of preventing the hanging that my application encounters when a concurrent update occurs.
I'll take a look at cursor concurrency controls, but again, any guidance would be very helpful.
# 6
> to my connection method, but this doesn't seem to do
> much in the way of preventing the hanging that my
> application encounters when a concurrent update
> occurs.
No. It won't. You seem to be interpretting "serializable" as meaning "cause only one transaction to ever run at any one time", but it doesn't do that because that would completely and utterly cripple the database.
Deadlocks occur when your transaction needs a resource that some other transaction has locked, and your transaction has locked a resource that the other transaction needs in order to complete.
The only way to prevent deadlocks is to avoid the circumstance arising in the first place. The best way to do that is to organise your transactions so that they acquire and release resources in the same order.
Put it this way, if you don't know what a deadlock is you're not going to have much luck avoiding them.
# 7
I mean the application hangs when a concurrent update on the same row occurs.
My problem is that if an external application deletes a row from a table and has not yet committed, and my servlet tries to update/delete that same row (an unlikely situation, but one I have to consider). I suppose this just causes a hang, and not actual deadlock, and my original post was before I quite understood the difference.
I suppose my actual question is not how to prevent deadlock then, but how to stop my application from hanging. Perhaps there's some sort of timeout that I can set?
Sorry for being unclear, I admit I hadn't thoroughly read up on the subject before the first post. Although I do want to pose the question that, if in order to avoid deadlocks by organizing the transactions, how would one accomplish this if two independantly operating applications (my own and an external) are working with the same database?
Does one simply need to assume no one else has access to the database except through the application that you design?
# 8
nevermind, I was overthinking the problem, I didn't have to deal with an external application, just my own, which makes dealing with concurrent updates by different users far easier.Thanks for the help anyway
# 9
Just a thought, but did you ever actually commit the sqlplus update? If you didn't, it was never deadlocking, it was just blocked waiting for the resource to be released. The two situations are completely different.
# 10
Yeah, that's why I said I was mistaken when I asked for help with deadlock in my other reply, but again, I was overcomplicating the issue that I had to deal with. Thanks for the help anyways.
