From: 枫 <liufeng_leo@qq.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: High concurrency but simple updating causes deadlock
Date: Wed, 11 Jul 2018 22:16:09 +0800
Message-ID: <tencent_C83DA547F7960B5682DA4A773D344FFC2B07@qq.com> (raw)
Hi,
All.
Can anyone give me a hand?
I meet a problem:High concurrency but simple updating causes deadlock
1.System info
Linux version 4.8.0
Ubuntu 5.4.0-6ubuntu1~16.04.4
2.Pg info
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
Changes inpostgresql.conf:
max_connections = 1000 //100 to 1000
3.Database for test――2000 row same data，
ipcid | surdevip | surdevport | devfactory | surchanmode | surchannum | username | password | transprotocol | mediastreamtype | streamid | bsmvalid | smdevip | smdevport | smtransprotocol
------------+----------+------------+------------+-------------+------------+----------+----------+---------------+-----------------+----------+----------+---------+-----------+-----------------
320460291 | Name | 8000 | 100 | 100 | 100 | admin | 666666 | 100 | 100 | hello | 1 | smpIp | 666 | 17
168201188 | Name | 8000 | 100 | 100 | 100 | admin | 666666 | 100 | 100 | hello | 1 | smpIp | 666 | 27
1360154585 | Name | 8000 | 100 | 100 | 100 | admin | 666666 | 100 | 100 | hello | 1 | smpIp | 666 | 70
820068220 | Name | 8000 | 100 | 100 | 100 | admin | 666666 | 100 | 100 | hello | 1 | smpIp | 666 | 49
。。。。。。2k row totally
4.Operation：Multi-user thread update
Each thread do the same cmd : Pgexc(“UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100”)
5.Error info
Error info in my code
ERROR: [func:insetDB line:1284]DB_Table_Update
ERROR: [func:DB_Table_Update line:705]PQexec(UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100) : ERROR: deadlock detected
DETAIL: Process 2366 waits for ShareLock on transaction 12316; blocked by process 2368.
Process 2368 waits for ShareLock on transaction 12289; blocked by process 2342.
Process 2342 waits for ExclusiveLock on tuple (9,1) of relation 18639 of database 18638; blocked by process 2366.
HINT: See server log for query details.
CONTEXT: while locking tuple (9,1) in relation "test6_chan_list_info"
Error info in pg log
ERROR: deadlock detected
DETAIL: Process 10938 waits for ExclusiveLock on tuple (1078,61) of relation 18639 of database 18638; blocked by process 10911.
Process 10911 waits for ShareLock on transaction 19494; blocked by process 10807.
Process 10807 waits for ShareLock on transaction 19560; blocked by process 10938.
Process 10938: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
Process 10911: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
Process 10807: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
HINT: See server log for query details.
STATEMENT: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
ERROR: deadlock detected
DETAIL: Process 10939 waits for ShareLock on transaction 19567; blocked by process 10945.
Process 10945 waits for ShareLock on transaction 19494; blocked by process 10807.
Process 10807 waits for ExclusiveLock on tuple (279,1) of relation 18639 of database 18638; blocked by process 10939.
Process 10939: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
Process 10945: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
Process 10807: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
HINT: See server log for query details.
CONTEXT: while locking tuple (279,1) in relation "test6_chan_list_info"
STATEMENT: UPDATE TEST6_CHAN_LIST_INFO SET streamId= 'hello', smDevPort= '666' WHERE transProtocol=100
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?
thanks very much!!!
Yours,
Leo from China
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: liufeng_leo@qq.com
Subject: Re: High concurrency but simple updating causes deadlock
In-Reply-To: <tencent_C83DA547F7960B5682DA4A773D344FFC2B07@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox