Re: High concurrency but simple updating causes deadlock

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(at)gmail(dot)com

> On Jul 11, 2018, at 16:16, 枫 <liufeng_leo(at)qq(dot)com> wrote:
>
> 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