wood burning stoves 2.0*
The moose likes JDBC and the fly likes DeadLock Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DeadLock" Watch "DeadLock" New topic
Author

DeadLock

rudresh kumar
Ranch Hand

Joined: Jan 04, 2006
Posts: 83
HI,

I have a application where in users fill and form as well as view their filled forms , i have different views in my application, like by name,by date.... What is the issue i am facing is the database is getting locked saying "dead lock" happened, When some users are filling the forms others viewing their applications

Later i changed the select queries to "Read Only" But even then i am having the same issue.

Can some one help me on this

Thanks
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

What type of database is it?
How do you reserve a record for editing (do you add for update when selecting)?

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
you can avoid this if you research the use of old and new.

Pessimistic Lock you selected row before you updated (Jan's suggestion)

SELECT * FROM my_table
WHERE column1 = ld.column1
AND column2 = ld.column2
FOR UPDATE NOWAIT;

There are 3 outcomes: the row is locked and returned to you for update, you get resource busy because some else has locked it, or you don't get the row back because somebody has already changed the row.


Optimistic Lock

UPDATE my_table
set myColumn1 = :new_column1, column2 = :new_column2
WHERE column1 = ld_column1
AND column2 = ld_column2

You have two outcomes: the row is updated, or you get 0 updates because somebody has already updated the row (i.e, your update may now need to be an insert).
rudresh kumar
Ranch Hand

Joined: Jan 04, 2006
Posts: 83
I am using Db2, here users will be using views and forms in parallel, may be even in two windows. How to make select queries doesn't lock the tables.

what i suspect is Select queries are locking the tables

Thanks
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by rudresh kumar:
I am using Db2, here users will be using views and forms in parallel, may be even in two windows. How to make select queries doesn't lock the tables.

what i suspect is Select queries are locking the tables

Thanks


You've already stated you have changed the Selects to be Read Only... this should allow dirty reads.

With or without read-only... a deadlock will not happen if only select statements are being executed against the data.

If you want the long version... Here's the DB2 8 guide for binds.

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.apsg/lksplan.htm
[ October 27, 2007: Message edited by: Paul Campbell ]
rudresh kumar
Ranch Hand

Joined: Jan 04, 2006
Posts: 83
HI,

I do agree that this is not because of repeatable select queries. It is only because of insert/update queries which is running on the same tables when select queries too are running on same tables.

But my question was even after giving READ ONLY how can the select queries lock the table, which results in deadlock (when insert/update queries run at same time)

Thanks
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by rudresh kumar:
HI,

I do agree that this is not because of repeatable select queries. It is only because of insert/update queries which is running on the same tables when select queries too are running on same tables.

But my question was even after giving READ ONLY how can the select queries lock the table, which results in deadlock (when insert/update queries run at same time)

Thanks


The lock occurs because two SQL transactions are attempting to lock the same record for updating.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: DeadLock
 
Similar Threads
nested tiles
Weblogic application lockup
Weblogic application lockup
Validation for included forms.
Multiple browser windows corupting sessions