Re: Dead lock happening when using update query

[Benoit St-Jean]

Can you :

1) Post the SHOW CREATE TABLE for all the tables involved
2) How many records do you have in that table?
3) Why do you use a temporary table?
4) What do you mean by 5 users per second? 5 new connections to the database per second? 5 users connected at any time? 5 calls to your stored procedure per second?
5) Can you post the code of your stored procedure
6) What is your current isolation level?

Now, correct me if I'm wrong:

a) You are SELECTing a record based on column1 and column 2
SELECT * FROM yourtable WHERE column1 = 383 AND column2 = 2112;

b) You are also SELECTing the "inversed record"
SELECT * FROM yourtable WHERE column1 = 2112 AND column2 = 383;

c) You update the column3 of the first record to some value, e.g 'Z'
d) You update the column3 of the second record to some other value, e.g 'W'


Am I right?