Sfrgttk

Why is "la Gestapo" feminine?

10 year ban after applying for a UK student visa

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

Homology of the fiber

label a part of commutative diagram

How to read string as hex number in bash?

Print last inputted byte

Turning a hard to access nut?

How can an organ that provides biological immortality be unable to regenerate?

Exposing a company lying about themselves in a tightly knit industry: Is my career at risk on the long run?

Unfrosted light bulb

Knife as defense against stray dogs

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

What is the reasoning behind standardization (dividing by standard deviation)?

Why does Surtur say that Thor is Asgard's doom?

How to find the largest number(s) in a list of elements, possibly non-unique?

pipe commands inside find -exec?

Is xar preinstalled on macOS?

Help with identifying unique aircraft over NE Pennsylvania

Would this string work as string?

Do people actually use the word "kaputt" in conversation?

Do I need to convey a moral for each of my blog post?

is this saw blade faulty?

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

Differences of MySQL locking for update with WHERE and IN

LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?Cannot update certain rows in innodb tablesMySQL InnoDB locks primary key on delete even in READ COMMITTEDHow to decipher the transaction message in innodb status log.Clarification on mysql deadlock situationWhy does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock?Mysql will no longer start after DB import and password changeGot deadlock for multiple update on same rowDeadlock on MySQL insert statmentsOverly eager explicit row locking causes deadlock

0

So I have a table with id as the primary column. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.

Example Table:

+----+--------------------------------------+-------------------------+

| id | some_val                             | created_at              |

+----+--------------------------------------+-------------------------+

|  1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |

|  2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |

|  3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |

+----+--------------------------------------+-------------------------+

Trans1:

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

INNODB Status

---TRANSACTION 10750, ACTIVE 4 sec

3 lock struct(s), heap size 360, 4 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root

Trans2:

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

INNODB Status

---TRANSACTION 10854, ACTIVE 11 sec

2 lock struct(s), heap size 360, 3 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root

Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).

I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.

mysql locking

share

asked 3 mins ago

eduardeveduardev

1011

New contributor

eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

So I have a table with id as the primary column. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.

Example Table:

+----+--------------------------------------+-------------------------+

| id | some_val                             | created_at              |

+----+--------------------------------------+-------------------------+

|  1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |

|  2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |

|  3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |

+----+--------------------------------------+-------------------------+

Trans1:

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

INNODB Status

---TRANSACTION 10750, ACTIVE 4 sec

3 lock struct(s), heap size 360, 4 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root

Trans2:

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

INNODB Status

---TRANSACTION 10854, ACTIVE 11 sec

2 lock struct(s), heap size 360, 3 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root

Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).

I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.

mysql locking

share

asked 3 mins ago

eduardeveduardev

1011

New contributor

eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

So I have a table with id as the primary column. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.

Example Table:

+----+--------------------------------------+-------------------------+

| id | some_val                             | created_at              |

+----+--------------------------------------+-------------------------+

|  1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |

|  2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |

|  3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |

+----+--------------------------------------+-------------------------+

Trans1:

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

INNODB Status

---TRANSACTION 10750, ACTIVE 4 sec

3 lock struct(s), heap size 360, 4 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root

Trans2:

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

INNODB Status

---TRANSACTION 10854, ACTIVE 11 sec

2 lock struct(s), heap size 360, 3 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root

Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).

I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.

mysql locking

share

asked 3 mins ago

eduardeveduardev

1011

New contributor

eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

+----+--------------------------------------+-------------------------+

| id | some_val                             | created_at              |

+----+--------------------------------------+-------------------------+

|  1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |

|  2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |

|  3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |

+----+--------------------------------------+-------------------------+

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

---TRANSACTION 10750, ACTIVE 4 sec

3 lock struct(s), heap size 360, 4 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

---TRANSACTION 10854, ACTIVE 11 sec

2 lock struct(s), heap size 360, 3 row lock(s)

MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root

share

asked 3 mins ago

eduardeveduardev

1011

New contributor

eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 3 mins ago

eduardeveduardev

1011

New contributor

eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

eduardeveduardev

1011

New contributor

eduardev is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 3 mins ago

eduardeveduardev

1011