pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)?

Started by Alexandru Lazarevover 6 years ago3 messagesgeneral

Alexandru Lazarev

alexandru.lazarev@gmail.com

over 6 years ago

Hi Community,
I receive locking failure on pg_advisory_lock, I do deadlock condition and
receive following:
- - -
ERROR: deadlock detected
SQL state: 40P01
Detail: Process 240828 waits for ExclusiveLock on advisory lock [
*1167570,16820923,3422556162,1*]; blocked by process 243637.
Process 243637 waits for ExclusiveLock on advisory lock [
*1167570,16820923,3422556161,1*]; blocked by process 240828.
- - -
I do from Tx1:
select pg_advisory_lock(72245317596090369);
select pg_advisory_lock(72245317596090370);
and from Tx2:
select pg_advisory_lock(72245317596090370);
select pg_advisory_lock(72245317596090369);

where long key is following: 72245317596090369-> HEX 0x0100*AABBCC001001*
where 1st byte (highest significance "0x01") is namespace masked with MAC
Address " *AABBCC001001*", but in error i see 4 numbers - what is their
meaning?
I deducted that 2nd ( *16820923* .) HEX 0x100AABB, 1st half of long key)
and 3rd is ( *3422556161* -> HEX 0xCC001001, 2nd half of long key)
but what are 1st ( *1167570* ) and 4th (*1*) numbers?

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=icon>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

laurenz.albe@cybertec.at

over 6 years ago

In reply to: Alexandru Lazarev (#1)

Re: pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)?

On Fri, 2019-07-19 at 21:15 +0300, Alexandru Lazarev wrote:

I receive locking failure on pg_advisory_lock, I do deadlock condition and receive following:
- - -
ERROR: deadlock detected
SQL state: 40P01
Detail: Process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1]; blocked by process 243637.
Process 243637 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556161,1]; blocked by process 240828.
- - -
I do from Tx1:
select pg_advisory_lock(72245317596090369);
select pg_advisory_lock(72245317596090370);
and from Tx2:
select pg_advisory_lock(72245317596090370);
select pg_advisory_lock(72245317596090369);

where long key is following: 72245317596090369-> HEX 0x0100AABBCC001001
where 1st byte (highest significance "0x01") is namespace masked with MAC Address " AABBCC001001", but in error i see 4 numbers - what is their meaning?
I deducted that 2nd ( 16820923 .) HEX 0x100AABB, 1st half of long key) and 3rd is ( 3422556161 -> HEX 0xCC001001, 2nd half of long key)
but what are 1st ( 1167570 ) and 4th (1) numbers?

See this code in src/backend/utils/adt/lockfuncs.c:

/*
* Functions for manipulating advisory locks
*
* We make use of the locktag fields as follows:
*
* field1: MyDatabaseId ... ensures locks are local to each database
* field2: first of 2 int4 keys, or high-order half of an int8 key
* field3: second of 2 int4 keys, or low-order half of an int8 key
* field4: 1 if using an int8 key, 2 if using 2 int4 keys
*/
#define SET_LOCKTAG_INT64(tag, key64) \
SET_LOCKTAG_ADVISORY(tag, \
MyDatabaseId, \
(uint32) ((key64) >> 32), \
(uint32) (key64), \
1)
#define SET_LOCKTAG_INT32(tag, key1, key2) \
SET_LOCKTAG_ADVISORY(tag, MyDatabaseId, key1, key2, 2)

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Alexandru Lazarev

alexandru.lazarev@gmail.com

over 6 years ago

In reply to: Laurenz Albe (#2)

Re: pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)?

Thanks. Question closed. :)

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=icon>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Fri, Jul 19, 2019 at 10:27 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Show quoted text

On Fri, 2019-07-19 at 21:15 +0300, Alexandru Lazarev wrote:

I receive locking failure on pg_advisory_lock, I do deadlock condition

and receive following:

- - -
ERROR: deadlock detected
SQL state: 40P01
Detail: Process 240828 waits for ExclusiveLock on advisory lock

[1167570,16820923,3422556162,1]; blocked by process 243637.

Process 243637 waits for ExclusiveLock on advisory lock

[1167570,16820923,3422556161,1]; blocked by process 240828.

- - -
I do from Tx1:
select pg_advisory_lock(72245317596090369);
select pg_advisory_lock(72245317596090370);
and from Tx2:
select pg_advisory_lock(72245317596090370);
select pg_advisory_lock(72245317596090369);

where long key is following: 72245317596090369-> HEX 0x0100AABBCC001001
where 1st byte (highest significance "0x01") is namespace masked with

MAC Address " AABBCC001001", but in error i see 4 numbers - what is their
meaning?

I deducted that 2nd ( 16820923 .) HEX 0x100AABB, 1st half of long key)

and 3rd is ( 3422556161 -> HEX 0xCC001001, 2nd half of long key)

but what are 1st ( 1167570 ) and 4th (1) numbers?

See this code in src/backend/utils/adt/lockfuncs.c:

/*
* Functions for manipulating advisory locks
*
* We make use of the locktag fields as follows:
*
* field1: MyDatabaseId ... ensures locks are local to each database
* field2: first of 2 int4 keys, or high-order half of an int8 key
* field3: second of 2 int4 keys, or low-order half of an int8 key
* field4: 1 if using an int8 key, 2 if using 2 int4 keys
*/
#define SET_LOCKTAG_INT64(tag, key64) \
SET_LOCKTAG_ADVISORY(tag, \
MyDatabaseId, \
(uint32) ((key64) >> 32), \
(uint32) (key64), \
1)
#define SET_LOCKTAG_INT32(tag, key1, key2) \
SET_LOCKTAG_ADVISORY(tag, MyDatabaseId, key1, key2, 2)

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com