Subject: Re: High concurrency but simple updating causes deadlock
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Content-Type: text/plain; charset=utf-8
From: Roman Konoval <rkonoval@gmail.com>
In-Reply-To: <tencent_C83DA547F7960B5682DA4A773D344FFC2B07@qq.com>
Date: Thu, 12 Jul 2018 12:55:09 +0200
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <2828B266-ED39-4D1F-A1F2-3F28B4125EAD@gmail.com>
References: <tencent_C83DA547F7960B5682DA4A773D344FFC2B07@qq.com>
To: =?utf-8?B?5p6r?= <liufeng_leo@qq.com>
Precedence: bulk

In this case this happens because the update modifies several rows and =
different transactions may try to modify those rows (and obtain locks =
for them) in different order.
E.g. one transaction first gets row 1 and then row 2, and the second =
transaction first updates row 2 and then row 1.

The only way to overcome this that I know is to first to select for =
update with order by clause so that all transactions lock rows in the =
same order and do not cause deadlock conflicts.

Regards,
Roman Konoval
rkonoval@gmail.com


> On Jul 11, 2018, at 16:16, =E6=9E=AB <liufeng_leo@qq.com> wrote:
>=20
> Hi,
>=20
> All.
> Can anyone give me a hand?
>=20
> I meet a problem:High concurrency but simple updating causes deadlock
>=20
> 1.System info
>=20
> Linux version 4.8.0
>=20
> Ubuntu 5.4.0-6ubuntu1~16.04.4
>=20
> 2.Pg info
>=20
> PostgreSQL 9.5.12 on i686-pc-linux-gnu, compiled by gcc (Ubuntu =
5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 32-bit
>=20
> Changes inpostgresql.conf:
> max_connections =3D 1000  //100 to 1000
>=20
> 3.Database for test=E2=80=94=E2=80=942000 row same data=EF=BC=8C
>=20
>    ipcid    | surdevip | surdevport | devfactory | surchanmode | =
surchannum | username | password | transprotocol | mediastreamtype | =
streamid | bsmvalid | smdevip | smdevport | smtransprotocol
>=20
> =
------------+----------+------------+------------+-------------+----------=
--+----------+----------+---------------+-----------------+----------+----=
------+---------+-----------+-----------------
>=20
>   320460291 | Name     |       8000 |        100 |         100 |       =
 100 | admin    | 666666   |           100 |             100 | hello    =
|        1 | smpIp   |       666 |              17
>=20
>   168201188 | Name     |       8000 |        100 |         100 |       =
 100 | admin    | 666666   |           100 |             100 | hello    =
|        1 | smpIp   |       666 |              27
>=20
> 1360154585 | Name     |       8000 |        100 |         100 |        =
100 | admin    | 666666   |           100 |             100 | hello    | =
       1 | smpIp   |       666 |              70
>=20
>   820068220 | Name     |       8000 |        100 |         100 |       =
 100 | admin    | 666666   |           100 |             100 | hello    =
|        1 | smpIp   |       666 |              49
>=20
> =E3=80=82=E3=80=82=E3=80=82=E3=80=82=E3=80=82=E3=80=822k row totally
>=20
>  4.Operation=EF=BC=9AMulti-user thread update
> Each thread do the same cmd : Pgexc(=E2=80=9CUPDATE =
TEST6_CHAN_LIST_INFO    SET streamId=3D 'hello', smDevPort=3D '666' =
WHERE  transProtocol=3D100=E2=80=9D)
>=20
> =20
>=20
> 5.Error info
>=20
> Error info in my code
>=20
> ERROR: [func:insetDB line:1284]DB_Table_Update
>=20
> ERROR: [func:DB_Table_Update line:705]PQexec(UPDATE =
TEST6_CHAN_LIST_INFO    SET streamId=3D 'hello', smDevPort=3D '666' =
WHERE  transProtocol=3D100) : ERROR:  deadlock detected
>=20
> DETAIL:  Process 2366 waits for ShareLock on transaction 12316; =
blocked by process 2368.
>=20
> Process 2368 waits for ShareLock on transaction 12289; blocked by =
process 2342.
>=20
> Process 2342 waits for ExclusiveLock on tuple (9,1) of relation 18639 =
of database 18638; blocked by process 2366.
>=20
> HINT:  See server log for query details.
>=20
> CONTEXT:  while locking tuple (9,1) in relation "test6_chan_list_info"
>=20
> Error info in pg log
>=20
> ERROR:  deadlock detected
>=20
> DETAIL:  Process 10938 waits for ExclusiveLock on tuple (1078,61) of =
relation 18639 of database 18638; blocked by process 10911.
>=20
>         Process 10911 waits for ShareLock on transaction 19494; =
blocked by process 10807.
>=20
>         Process 10807 waits for ShareLock on transaction 19560; =
blocked by process 10938.
>=20
>         Process 10938: UPDATE TEST6_CHAN_LIST_INFO      SET streamId=3D =
'hello', smDevPort=3D '666' WHERE  transProtocol=3D100
>=20
>         Process 10911: UPDATE TEST6_CHAN_LIST_INFO      SET streamId=3D =
'hello', smDevPort=3D '666' WHERE  transProtocol=3D100
>=20
>         Process 10807: UPDATE TEST6_CHAN_LIST_INFO      SET streamId=3D =
'hello', smDevPort=3D '666' WHERE  transProtocol=3D100
>=20
> HINT:  See server log for query details.
>=20
> STATEMENT:  UPDATE TEST6_CHAN_LIST_INFO SET streamId=3D 'hello', =
smDevPort=3D '666' WHERE  transProtocol=3D100
>=20
> ERROR:  deadlock detected
>=20
> DETAIL:  Process 10939 waits for ShareLock on transaction 19567; =
blocked by process 10945.
>=20
>         Process 10945 waits for ShareLock on transaction 19494; =
blocked by process 10807.
>=20
>         Process 10807 waits for ExclusiveLock on tuple (279,1) of =
relation 18639 of database 18638; blocked by process 10939.
>=20
>         Process 10939: UPDATE TEST6_CHAN_LIST_INFO      SET streamId=3D =
'hello', smDevPort=3D '666' WHERE  transProtocol=3D100
>=20
>         Process 10945: UPDATE TEST6_CHAN_LIST_INFO      SET streamId=3D =
'hello', smDevPort=3D '666' WHERE  transProtocol=3D100
>=20
>         Process 10807: UPDATE TEST6_CHAN_LIST_INFO      SET streamId=3D =
'hello', smDevPort=3D '666' WHERE  transProtocol=3D100
>=20
> HINT:  See server log for query details.
>=20
> CONTEXT:  while locking tuple (279,1) in relation =
"test6_chan_list_info"
>=20
> STATEMENT:  UPDATE TEST6_CHAN_LIST_INFO SET streamId=3D 'hello', =
smDevPort=3D '666' WHERE  transProtocol=3D100
> =20
> 6.my quetion
> 6.1.is it possible meet dead lock with high conurrency simple update?
> 6.2.if yes, why,and how to avoid?
> =20
> thanks very much!!!
> =20
> Yours,
> =20
> Leo from China