Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

here is my code:

mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ($row[0]+$k)";
$r=mysql_query($q);
}
mysql_query("commit");

if i run code with 2 browser if id column is not indexed code run correctly- second session wait until first session commit -.

but if id column is indexed i get this error "mysql_fetch_array() expects parameter 1 to be resource, boolean given " on second browser - waiting session -.

when i use mysql_error() before mysql_fetch_array() i get this error "Deadlock found when trying to get lock; try restarting transaction". why?

share|improve this question

1 Answer 1

SELECT FOR UPDATE tends to do row level locking in a deep way we tend to take for granted: Locking the corresponding index entries as well.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data at the index level.

You also have special cases of SELECT where you can lock specific rows on demand.

Look at this issue from another angle, "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT?" The answer to that can be Yes under a certain condition: If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

share|improve this answer
    
thank u. when i reduce for loop to 15000, i cant see any problem . what is problem with many insert like 100000 insert? see line 5 of code. –  a.v Mar 18 '12 at 10:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.