Deadlock happens when sink to mysql

[徐涛]

Hi Experts,

I use the following sql, and sink to mysql, 

    select

    album_id, date

    count(1)

from

    coupon_5_discount_date_conv

group by

    album_id, date;

when sink to mysql, the following SQL is executed: insert into xxx (c1,c2,c3) values (?,?,?) on duplicate key update c1=VALUES(c1),c2=VALUES(c2), c3=VALUES(c3)

        The engine is InnoDB, column c1,c2 is unique key, the isolation level is READ COMMITTED. But in the log a deadlock exception happens.

As I know, because the unique key exists, only the line lock will be applied, no gap lock will be applied. And due to a group by sentence, the same unique key should be written by the same thread. So in this case, why the dead lock should happened? Could anyone help me? Thanks a lot.

Best

Henry

[miki haiat]

can you share your entire code please 

On Mon, Nov 19, 2018 at 4:03 PM 徐涛 <[hidden email]> wrote:

Hi Experts,

I use the following sql, and sink to mysql, 

    select

    album_id, date

    count(1)

from

    coupon_5_discount_date_conv

group by

    album_id, date;

when sink to mysql, the following SQL is executed: insert into xxx (c1,c2,c3) values (?,?,?) on duplicate key update c1=VALUES(c1),c2=VALUES(c2), c3=VALUES(c3)

        The engine is InnoDB, column c1,c2 is unique key, the isolation level is READ COMMITTED. But in the log a deadlock exception happens.

As I know, because the unique key exists, only the line lock will be applied, no gap lock will be applied. And due to a group by sentence, the same unique key should be written by the same thread. So in this case, why the dead lock should happened? Could anyone help me? Thanks a lot.

Best

Henry

[Fabian Hueske-2]

Hi,

Which TableSource and TableSink do you use?

Best, Fabian

Am Mo., 19. Nov. 2018 um 15:39 Uhr schrieb miki haiat <[hidden email]>:

can you share your entire code please 

On Mon, Nov 19, 2018 at 4:03 PM 徐涛 <[hidden email]> wrote:

Hi Experts,

I use the following sql, and sink to mysql, 

    select

    album_id, date

    count(1)

from

    coupon_5_discount_date_conv

group by

    album_id, date;

when sink to mysql, the following SQL is executed: insert into xxx (c1,c2,c3) values (?,?,?) on duplicate key update c1=VALUES(c1),c2=VALUES(c2), c3=VALUES(c3)

        The engine is InnoDB, column c1,c2 is unique key, the isolation level is READ COMMITTED. But in the log a deadlock exception happens.

As I know, because the unique key exists, only the line lock will be applied, no gap lock will be applied. And due to a group by sentence, the same unique key should be written by the same thread. So in this case, why the dead lock should happened? Could anyone help me? Thanks a lot.

Best

Henry