Will these two queries result in a deadlock if executed in sequence?

This is almost certainly the cause of my other question, but I thought it was worth separating the two as I have a hypothesis based on the following log that I would love to have falsified or verified.

My hypothesis is that the other deadlock is actually a result of the following queries, with the original query hidden based on my understanding the innodb status only shows the most recent transactions (is this correct?).

Based on the log, I have checked our code and found the following two queries executed in sequence:

db.Execute("UPDATE people SET iphone_device_id=NULL WHERE iphone_device_id=@0 AND people_id<>@1", DeviceID, m_User.people_id);
// I have hard coded this query in this snippet to simplify things
db.Execute("UPDATE people SET company_id = 444, name = 'Dad', password = '', temp_password = NULL, reset_password_hash = NULL, email = '@gmail.com', phone = NULL, mobile = NULL, iphone_device_id = 'iphone:', iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47', location_lat = , location_long = , gps_strength = 66, picture_blob_id = 1661, authority = 1, active = 1, date_created = '2011-03-20 19:18:34', last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55, battery_state = 'unplugged' WHERE people_id = 666");

Where db.Execute is basically doing a ExecuteNonQuery on a System.Data DbCommand object.

So will this sequence of queries result in a deadlock? My hypothesis is that the two different field orders in the two queries causes the issue? Can I wrap the two queries up in a transaction to resolve the deadlock?

```
------------------------
LATEST DETECTED DEADLOCK
------------------------
110607 11:15:01
*** (1) TRANSACTION:
TRANSACTION 0 45674214, ACTIVE 0 sec, OS thread id 2584 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s)
MySQL thread id 109, query id 4044915 localhost 127.0.0.1 famdev Searching rows for update
UPDATE people SET ip

Even though the statements are executed in sequence, if they exist inside the same transaction, you must issue some kind of checkpoint between queries or tweek the transaction isolation level before starting the transaction.

There are four values for tx_isolation:

• READ-UNCOMMITTED

• READ-COMMITTED

• REPEATABLE-READ (default)

• SERIALIZABLE

You could set the transaction isolation three(3) ways:

Write this in /etc/my.cnf and restart mysql

[mysqld]
autocommit=0
transaction-isolation = READ-UNCOMMITTED

or you could set it within the DB Connection itself before starting any new transaction:

db.Execute("SET tx_isolation = 'READ-UNCOMMITTED'");
db.Execute("SET autocommit = 0");

or disable autocommit using .NET protocols (Disclaimer: I'm not a .NET developer)

Although I am leaning toward READ-UNCOMMITTED to allow "dirty reads", you must experiment with other transaction isolation levels at some point to see which one has the desired effect.

Give it a Try !!!

StackExchange Database Administrators Q#3223, answer score: 7