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 test2000 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| 66 | 100 | 100 | hello|1 | smpIp | 666 | 17 168201188 | Name | 8000 |100 | 100 |100 | admin| 66 | 100 | 100 | hello|1 | smpIp | 666 | 27 1360154585 | Name | 8000 |100 | 100 |100 | admin| 66 | 100 | 100 | hello|1 | smpIp | 666 | 70 820068220 | Name | 8000 |100 | 100 |100 | admin| 66 | 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_INFOSET 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_INFOSET 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
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 rkono...@gmail.com > On Jul 11, 2018, at 16:16, 枫 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| 66 | 100 | 100 | hello|1 | > smpIp | 666 | 17 > > 168201188 | Name | 8000 |100 | 100 |100 | > admin| 66 | 100 | 100 | hello|1 | > smpIp | 666 | 27 > > 1360154585 | Name | 8000 |100 | 100 |100 | > admin| 66 | 100 | 100 | hello|1 | > smpIp | 666 | 70 > > 820068220 | Name | 8000 |100 | 100 |100 | > admin| 66 | 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_INFOSET > 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
Re: performance statistics monitoring without spamming logs
On Tue, Jul 10, 2018 at 11:38 AM, Justin Pryzby wrote: > > > 2. Make stats available in `pg_stat_statements` (or alternate view that > > could be joined on). The block stats are already available here, but > > others like CPU usage, page faults, and context switches are not. > > pg_stat_statements is ./contrib/pg_stat_statements/pg_stat_statements.c > which is 3k LOC. > > getrusage stuff and log_*_stat stuff is in src/backend/tcop/postgres.c Before you start implementing something here, take a look at pg_stat_kcache [0] Which already aims to collect a few more system statistics than what pg_stat_statements provides today, and might be a good basis to extend from. It might also be worth to look at pg_stat_activity wait event sampling to determine where a system spends time, see e.g. pg_wait_sampling [1] for one approach to this. [0]: https://github.com/powa-team/pg_stat_kcache [1]: https://github.com/postgrespro/pg_wait_sampling Best, Lukas -- Lukas Fittl
