Re: How to handle deadlocks ?

On Wed, 12 Jun 96 21:26:54 GMT, joachim_at_softouch.bc.ca (Joachim Achtzehnter) wrote:

>>>> Thomas J. Kyte <tkyte_at_us.oracle.com> wrote in response to
 

>>>>>> <joachim_at_softouch.bc.ca> Joachim Achtzehnter's ramblings:
>
>Joachim> If you follow a fixed locking order you can avoid most deadlock
>Joachim> situations.
>
>Thomas> Not really, and only if you employ full table level locks on data.
>
>Note, that I said *most* deadlocks can be avoided. Following a locking order
>is a standard design approach that can reduce the number of deadlocks
>considerably. Are you suggesting one should ignore this? If so, then I
>disagree with you on this point. Deadlocks are undesirable even in
>applications that are written to properly deal with deadlocks, when deadlocks
>occur you are wasting time and resources!

But for Ingress and the others to get consistent results they employ read locks (a query will place a shared read lock preventing updates on information simply read from the database). Are you suggesting that you not only describe what order to UPDATE tables in but also to READ? While you can control the order in which you update generally (you can update singleton rows in a very specific order if you want), you cannot control the order in which information is read, especially if any of your queries touch more then one row or join multiple tables together.

I've worked on lots of 500+ table schemas. How in the world do you describe the definitive way to update information in a large schema? Alphabetically maybe? How do you enforce this with dozens of developers? What about ad-hoc queries that now lock data (not in Oracle but in others) to get correct answers.

I myself prefer to work in a environment where deadlocks are mostly avoided by the databases underlying concurrency model rather then spend weeks/months working on update/read orders. What happens when your requirments change, when a new application is introduced, when multiple conflicting applications are working on the same data?

>
>Thomas> On a page level locking system, the problem is even worse, deadlocks
>Thomas> are unavoidable in a highly active environment. Since locking a row
>Thomas> locks some completely unrelated data, you have no control over what
>Thomas> you are locking. Escalation simply makes the problem even worse.
>
>It makes it somewhat worse, I agree. But it has also advantages over row
>locking, its just a different tradeoff. For example, with page locking, or
>more precisely with "access path locking" one can prevent the problem of ghost
>rows. With row locks you can't.
>

If you are relying on page level locking to achieve Isol 3 (phantom read protection) you are mistaken. All page level locking does is decrease concurrency. As soon as the transaction holding the locks commits, your transaction that was waiting on the block gets to see it. Page level locking does not prevent phantom reads in any way shape or form. Perhaps you have a unique definition of 'ghost rows'.

>Joachim> benchmarks may suggest Oracle provides better concurrency.
>
>Thomas> As does real world experience.
>
>Well, people with an email address of <tkyte_at_us.oracle.com> must say
>this, don't they? ;-)
>
>Joachim> But the downside is that the default Oracle locking does
>Joachim> not guarantee consistent multi-query transactions.
>
>Thomas> Neither do the others. Only if you turn on full repeatable reads
>Thomas> in the others do you get consistent multi-query transactions.
>
>Ok, I should not have generalized too much, I haven't used every RDBMS in the
>market. Let's say I have worked with some of Oracle's competitors in the past,
>and these products (Ingres in particular) provided full repeatable reads by
>*default*. The tradeoff is between correctness and performance. Oracle decided
>in favour of performance (benchmarks!), Ingres in favour of correctness
>(academic origins?). And in both cases I am talking about the default
>behaviour you get without manual intervention or changes to the configuration.
>You can change the behaviour in both Oracle and Ingres when needed.
>
>Thomas> In fact, the default mode in the other dbms's dont even gaurantee
>Thomas> SINGLE query consistency.
>
>Interesting. Which other dbms's do you mean? I would like to know because I
>would certainly want to stay away from those! But be careful with general
>statements like this, not all your competitors behave as you described.
>
>Joachim

I was talking about the databases that comprise somewhere between 85-90% of the market in UNIX.

Here's an example of what I mean:

Take for example a simple read only transaction

        select sum(sal) from emp;

Lets say we issue this query in Oracle, Sybase, or Informix. Lets also assume that emp spans multiple pages/blocks/rows. Lets also assume that all databases perform locking at the row level (the emp table is very wide and fits one row per 1,962 byte page so Sybase is locking a row at a time).

In these databases, people are constantly updating the sal column in the emp table. On would assume that any database would return a correct, consistent answer to such a simple query. Fact is, without modifying the query or changing the default mode in Sybase & Informix, you can not guarantee that a correct, consistent answer is returned.

The following timeline shows what I mean in Sybase or Informix (and probably ingress if you turn off repeatable read)

t1	"select sum from emp" reads page 1
t2	another user updates data on page 3 of the emp table, gives
	an extra $100 to someone
t3      that same update attempts to update data on page 1 but is blocked
	because the select is processing
t4	"select sum from emp" gives up page 1 and moves on to page 2
t5	update of page 1 completes now since select gives up locks. Update
	subtracts $100 from someone.
t6	"select sum from emp" gives up page 2 and tries to move on to page
	3.  "select sum from emp" is blocked from reading page 3 since
	an outstanding update has an exclusive lock on the data
t7	update completes, releases locks
t8	"select sum from emp" reads the new values on page 3 and computes
	the total sum.

And the answer is: exactly $100 MORE than any value in the database at ANY
		   point in time.  The "select sum from emp" read page 1 before
		   it changed and page 3 AFTER it changed.  The query saw the
		   $100 raise but not the $100 deduction.

The following timeline shows what would happen in Oracle:

t1	"select sum from emp" reads page 1
t2	another user updates data on page 3 of the emp table, gives
	an extra $100 to someone
t3      that same update modifies data (subtracts $100) on page 1.  
	Since readers don't block writes and writes don't block 
	reads, update succeeds. 
t4	"select sum from emp" finishes page 1 and moves on to page 2
t5	"select sum from emp" finishes page 2 and moves on to page
	3.  "select sum from emp" is NOT blocked from reading page 3. However,
	since page 3 was modified since the query began, a read aside is 
	performed to the rollback segment to read the value that was on 
	page 3 when the query began.
t6	update completes, releases locks

And the answer is: exactly what was in the database the moment the query began.
		   Not $100 more, Not $100 less but the correct, consistent
		   answer that was in the database at some point in time (when
		   the query began executing).

In Sybase you would have to "select ... HOLDLOCK" or goto isol 3 to achieve the same results.
In Informix you would have to set your transaction isolation level to repeatable read.
In Ingress apparently the default mode is repeatable read.

In either case, the above timeline would have resulted in a DEADLOCK in both Informix or Sybase (and Ingress) had the user gone to HOLDLOCK, isol 3 or repeatable read.

The reader would have deadlocked with the writer and either the query would have been aborted or the update would have been aborter. The reader would have kept the lock on page 1, blocking the update. The update would have kept the lock on page 3 blocking the read.

In order to get a consistent read-only statement in those databases, or any database that employs shared read locks when executing querys, other then Oracle you will introduce Queries that can and will deadlock with Updates/ Inserts/Deletes OR significantly reduced concurrency due to the fact that queries and updates will serialize (eg: not execute at the same time).

If you want to call that a benchmark feature, so be it. I call it real world performance, much less deadlocking, need for less rules/restrictions on applications accessing data, and so on.

>
>_____________________________
>joachim_at_softouch.bc.ca (work)
>joachim_at_wimsey.ca (home)

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Jun 17 1996 - 00:00:00 CEST