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 via email to