High concurrency but simple updating causes deadlock

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