• Post Reply Bookmark Topic Watch Topic
  • New Topic

Could you have Deadlock on a Select Query

 
bob morkos
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to prove to somoene that you can't have a deadlock on a JDBC Query. Could somoene help me in proving him wrong. As long as you close your connections carefully and dont use transaction than you should not have deadlock. I need arguments to be able to explain to him, thanks for your feedback.
 
Ernest Friedman-Hill
author and iconoclast
Sheriff
Posts: 24213
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mving to "Threads and Synchronization."
 
Don Morgan
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bob, do you mean that all requests to the database are only queries (no updates, no transactions) or that you have, say one thread doing only queries and another thread which can do updates and transactions?

If you have page level locking on the database then you can still have deadlocks in the latter case - one thread only doing queries and another doing updates and transactions. Basically this is because different operations may acquire the locks on different pages in a different order. For this case, look at the following sequence where Thread1 is doing updates and Thread2 is only doing queries.

Time t1: Thread1 locks page p1 to do update (exclusive lock)
Time t2: Thread2 locks page p2 while doing a select (sharable lock)
Time t3: Thread1 blocks waiting for an exclusive lock on page p2 so it can do its update
Time t4: Thread2 blocks waiting for a sharable lock on page p1 so it can finish its select, deadlocking with Thread1

Of course, if ALL connections to the database only ever query the data,i.e., always used sharable locks, then I agree with you and don't see how you could get deadlocks (of course, most system do updates too).
 
Henry Wong
author
Sheriff
Posts: 22542
109
C++ Chrome Eclipse IDE Firefox Browser Java jQuery Linux VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You don't actually need locks to deadlock -- a monitor is simply some data flag used to represent a lock. In theory, any data that is used to represent something that a thread can wait on can cause a deadlock. This includes database fields.

However, Don is correct -- as it will be impossible to deadlock if the database is readonly.

Henry
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!