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
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
