Sfrgttk

What is 露わになる affecting in the following sentence, '才能の持ち主' (持ち主 to be specific) or '才能'?

Does fire aspect on a sword, destroy mob drops?

Jem'Hadar, something strange about their life expectancy

What will the Frenchman say?

is this saw blade faulty?

Does convergence of polynomials imply that of its coefficients?

What are the rules for concealing thieves' tools (or items in general)?

Can other pieces capture a threatening piece and prevent a checkmate?

Do I need an EFI partition for each 18.04 ubuntu I have on my HD?

How old is Nick Fury?

Why didn’t Eve recognize the little cockroach as a living organism?

Norwegian Refugee travel document

Are hand made posters acceptable in Academia?

Exit shell with shortcut (not typing exit) that closes session properly

Hackerrank All Women's Codesprint 2019: Name the Product

Do native speakers use "ultima" and "proxima" frequently in spoken English?

Should I be concerned about student access to a test bank?

Why doesn't the fusion process of the sun speed up?

Homology of the fiber

"Marked down as someone wanting to sell shares." What does that mean?

Fair way to split coins

Hot air balloons as primitive bombers

What is it called when someone votes for an option that's not their first choice?

Animating wave motion in water

Why does the delete/update block each other in this case?

Concurrent MySQL updates hang with InnoDB (on Amazon RDS)How can I make a select statement get blocked?MySQL performance of trigger when many rows are affectedSet particular database connection temporarily to read-onlyInnoDB MVCC vs LockingWhy do temporal tables log the begin time of the transaction?How to atomically replace a subset of table data“Unrelated” INSERT and UPDATE blocking each otherSQL Deadlock graph - Find exact statement holding the lock and the time it startedInsert statement on one table blocking delete on another unrelated table on sql server

0

The following drives me crazy.

I have:

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id=1;  

id is the PK of the table.

These statements execute without blocking each other. Makes sense since they refer to different rows.

Next:

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2);  

These also execute without blocking.

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2,3,4);  

These block each other but not always! I can not understand this. These refer to different rows why do they block each other?

mysql locking transaction concurrency

share|improve this question

asked Apr 7 '14 at 22:23

JimJim

3262615

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

0

The following drives me crazy.

I have:

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id=1;  

id is the PK of the table.

These statements execute without blocking each other. Makes sense since they refer to different rows.

Next:

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2);  

These also execute without blocking.

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2,3,4);  

These block each other but not always! I can not understand this. These refer to different rows why do they block each other?

mysql locking transaction concurrency

share|improve this question

asked Apr 7 '14 at 22:23

JimJim

3262615

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

The following drives me crazy.

I have:

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id=1;  

id is the PK of the table.

These statements execute without blocking each other. Makes sense since they refer to different rows.

Next:

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2);  

These also execute without blocking.

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2,3,4);  

These block each other but not always! I can not understand this. These refer to different rows why do they block each other?

mysql locking transaction concurrency

share|improve this question

asked Apr 7 '14 at 22:23

JimJim

3262615

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id=1;  

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2);  

session1>begin;  

session2>begin;  

session2>update dummy set salary=1000 where id=11;  

session1>delete from dummy where id in (1,2,3,4);  

share|improve this question

asked Apr 7 '14 at 22:23

JimJim

3262615

share|improve this question

asked Apr 7 '14 at 22:23

JimJim

3262615

asked Apr 7 '14 at 22:23

JimJim

3262615

asked Apr 7 '14 at 22:23

JimJim

3262615

1 Answer
1

active

oldest

votes

0

_{Answer based on comments by a-horse-with-no-name}

You are probably being hit by MySQL's "gap locking" (locking rows that aren't there).

• 
  

  https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-gap-locks

  
  
  

  
  

  Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

  
  

  
  


• 
  

  https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

  
  
  

  
  

  MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks.

  
  

  
  

share|improve this answer

answered Feb 12 at 21:11

Comment ConverterComment Converter

1,3221325

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f62723%2fwhy-does-the-delete-update-block-each-other-in-this-case%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

_{Answer based on comments by a-horse-with-no-name}

You are probably being hit by MySQL's "gap locking" (locking rows that aren't there).

• 
  

  https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-gap-locks

  
  
  

  
  

  Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

  
  

  
  


• 
  

  https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

  
  
  

  
  

  MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks.

  
  

  
  

share|improve this answer

answered Feb 12 at 21:11

Comment ConverterComment Converter

1,3221325

add a comment | 

0

_{Answer based on comments by a-horse-with-no-name}

You are probably being hit by MySQL's "gap locking" (locking rows that aren't there).

• 
  

  https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-gap-locks

  
  
  

  
  

  Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

  
  

  
  


• 
  

  https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

  
  
  

  
  

  MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks.

  
  

  
  

share|improve this answer

answered Feb 12 at 21:11

Comment ConverterComment Converter

1,3221325

add a comment | 

_{Answer based on comments by a-horse-with-no-name}

You are probably being hit by MySQL's "gap locking" (locking rows that aren't there).

• 
  

  https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-gap-locks

  
  
  

  
  

  Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

  
  

  
  


• 
  

  https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

  
  
  

  
  

  MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks.

  
  

  
  

share|improve this answer

answered Feb 12 at 21:11

Comment ConverterComment Converter

1,3221325

share|improve this answer

answered Feb 12 at 21:11

Comment ConverterComment Converter

1,3221325

answered Feb 12 at 21:11

Comment ConverterComment Converter

1,3221325

answered Feb 12 at 21:11

Comment ConverterComment Converter

1,3221325