[Davical-general] Possible fix for deadlocks in database

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Davical-general] Possible fix for deadlocks in database

Egoitz Aurrekoetxea-2
Good afternoon,

As you know, when you are doing a massive upload to a collection, you end up by seeing in the logs the following errors :

[Sat Apr 02 18:09:49.814518 2016] [:error] [pid 53742] [client 192.148.167.11:21670] davical: LOG: [hidden email]: Query: QF: SQL error "40P01" - ERROR: deadlock detected DETALLE: Process 19727 waits for ShareLock on transaction 69330601; blocked by process 19728. Process 19728 waits for ExclusiveLock on tuple (0,29) of relation 1206940 of database 1206795; blocke
.
.
.
.
.
.

[Sat Apr 02 18:09:49.818981 2016] [:error] [pid 53742] [client 192.148.167.11:21670] davical: LOG: [hidden email]: Query: QF: SQL error "25P02" - ERROR: current transaction is aborted, commands ignored until end of transaction block"

Basically this seems to be because perhaps Davical was designed having in mind all clients behave uploading or deleting for instance... elements but, one by one.... but nowadays... some clients
work this way, and other ones, don't. You can easily adapt Sogo connector for behaving this proper way (and fix some other bug too by the way. You can download this patched by me version from
 https://www.saremail.com/docs/sogosarenet), but not for intance Apple contacts app (as it's not open source). In "El Capitan" it seems they have modified the CardDAV code by openning several
simultaneous connectionsto the CardDAV server for faster uploads. Happens the same (although in CalDAV) with Lightning, Calendar app in Mac... and perhaps some other too....

I have search in Google for a fix for this problem, but have not been able to find... just some ideas of Andrew McMillan saying he was thinking on writting something for fix the issue using memcached
and so... After reading that, I stayed thinking in it... and think have found a simplier solution to this problem (well, at least, simplier than using memcached for me). I wanted to share the following patch
for Davical 1.1.4 with you in order to know your opinion or contributing it. I know things could be perhaps better written but, this patch seems to work fine and could very easily be improved for working
even slightly better (although have not been able to make it fail, all massive uploads and removals have succeeded) if needed.

The patch is attached to this mail. Let me know your impressions about it :)

Best regards,



sarenet
Egoitz Aurrekoetxea
Departamento de sistemas
944 209 470
Parque Tecnológico. Edificio 103
48170 Zamudio (Bizkaia)

Antes de imprimir este correo electrónico piense si es necesario hacerlo.

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general

parche-anti-deadlocks.patch (16K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Possible fix for deadlocks in database

Egoitz Aurrekoetxea-2
Sorry, for applying it as you would easily see.. you can copy it to inc directory of Davical 1.1.4 and do a patch -p0 there...

El 3/4/16 a las 22:31, Egoitz Aurrekoetxea escribió:
Good afternoon,

As you know, when you are doing a massive upload to a collection, you end up by seeing in the logs the following errors :

[Sat Apr 02 18:09:49.814518 2016] [:error] [pid 53742] [client 192.148.167.11:21670] davical: LOG: [hidden email]: Query: QF: SQL error "40P01" - ERROR: deadlock detected DETALLE: Process 19727 waits for ShareLock on transaction 69330601; blocked by process 19728. Process 19728 waits for ExclusiveLock on tuple (0,29) of relation 1206940 of database 1206795; blocke
.
.
.
.
.
.

[Sat Apr 02 18:09:49.818981 2016] [:error] [pid 53742] [client 192.148.167.11:21670] davical: LOG: [hidden email]: Query: QF: SQL error "25P02" - ERROR: current transaction is aborted, commands ignored until end of transaction block"

Basically this seems to be because perhaps Davical was designed having in mind all clients behave uploading or deleting for instance... elements but, one by one.... but nowadays... some clients
work this way, and other ones, don't. You can easily adapt Sogo connector for behaving this proper way (and fix some other bug too by the way. You can download this patched by me version from
 https://www.saremail.com/docs/sogosarenet), but not for intance Apple contacts app (as it's not open source). In "El Capitan" it seems they have modified the CardDAV code by openning several
simultaneous connectionsto the CardDAV server for faster uploads. Happens the same (although in CalDAV) with Lightning, Calendar app in Mac... and perhaps some other too....

I have search in Google for a fix for this problem, but have not been able to find... just some ideas of Andrew McMillan saying he was thinking on writting something for fix the issue using memcached
and so... After reading that, I stayed thinking in it... and think have found a simplier solution to this problem (well, at least, simplier than using memcached for me). I wanted to share the following patch
for Davical 1.1.4 with you in order to know your opinion or contributing it. I know things could be perhaps better written but, this patch seems to work fine and could very easily be improved for working
even slightly better (although have not been able to make it fail, all massive uploads and removals have succeeded) if needed.

The patch is attached to this mail. Let me know your impressions about it :)

Best regards,



sarenet
Egoitz Aurrekoetxea
Departamento de sistemas
944 209 470
Parque Tecnológico. Edificio 103
48170 Zamudio (Bizkaia)

Antes de imprimir este correo electrónico piense si es necesario hacerlo.

--


sarenet
Egoitz Aurrekoetxea
Departamento de sistemas
944 209 470
Parque Tecnológico. Edificio 103
48170 Zamudio (Bizkaia)

Antes de imprimir este correo electrónico piense si es necesario hacerlo.

------------------------------------------------------------------------------
Transform Data into Opportunity.
Accelerate data analysis in your applications with
Intel Data Analytics Acceleration Library.
Click to learn more.
http://pubads.g.doubleclick.net/gampad/clk?id=278785471&iu=/4140
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [DAViCal-devel] Possible fix for deadlocks in database

Marten Gajda-2
Hi Egoitz,

would you mind translating the comments and messages to English? That
would make it easier to understand how it works when reviewing the patch.

cheers

Marten

Am 03.04.2016 um 22:35 schrieb Egoitz Aurrekoetxea:
> Sorry, for applying it as you would easily see.. you can copy it to
> inc directory of Davical 1.1.4 and do a patch -p0 there...
>
> El 3/4/16 a las 22:31, Egoitz Aurrekoetxea escribió:

--
Marten Gajda
CEO

dmfs GmbH
Schandauer Straße 34
01309 Dresden
GERMANY

phone: +49 177 4427167
email: [hidden email]

Managing Director: Marten Gajda
Registered address: Dresden
Registered No.: AG Dresden HRB 34881
VAT Reg. No.: DE303248743


------------------------------------------------------------------------------
_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [DAViCal-devel] Possible fix for deadlocks in database

Egoitz Aurrekoetxea-2
No problem on translating... but can explain what it does very easily....

If you try to do a PUT or a DELETE on my Davical server :

1 - It's checked if a file called as the own username in Davical, does not exist in a folder. It creates it in this case, and with the PID of the creator proccess inside the plain text file. This write down
of the PID is done with exclusive write lock.

2.- If a file called as the own username in Davical does exist in a folder, it's content is read to see which PID created it (that pid is written inside).

3.- If the pid of the reading proccess and written inside the pid are the same... the proccess goes on...

4.- If the pid of the reading proccess and written inside the pid are not the same... the proccess returns to point 2 and gets looped in the 2,3 and 4 steps till step 3 succeeds.

5.- When PID read from file and the own proccess's one are the same, and all tasks (all code instructions pending) have been completed, this token filename (whose name is the username) is removed from the directory.

This way I avoided deadlocks....

That's how it works...

Bye Marten!!


El 4/4/16 a las 22:44, Marten Gajda escribió:
Hi Egoitz,

would you mind translating the comments and messages to English? That
would make it easier to understand how it works when reviewing the patch.

cheers

Marten

Am 03.04.2016 um 22:35 schrieb Egoitz Aurrekoetxea:
Sorry, for applying it as you would easily see.. you can copy it to
inc directory of Davical 1.1.4 and do a patch -p0 there...

El 3/4/16 a las 22:31, Egoitz Aurrekoetxea escribió:

    

--


Egoitz Aurrekoetxea
Departamento de sistemas
944 209 470
Parque Tecnológico. Edificio 103
48170 Zamudio (Bizkaia)

Antes de imprimir este correo electrónico piense si es necesario hacerlo.

Re: [DAViCal-devel] Possible fix for deadlocks in database
Asunto:
Re: [DAViCal-devel] Possible fix for deadlocks in database
De:
Egoitz Aurrekoetxea <[hidden email]>
Fecha:
5/4/16 12:26
Para:
Marten Gajda <[hidden email]>, [hidden email], [hidden email], [hidden email]
FCC:
imap://egoitz%[hidden email]/Sent
X-Identity-Key:
id1
X-Account-Key:
account1
Referencias:
<[hidden email]> <[hidden email]> <[hidden email]>
Organización:
Sarenet S.A.U.
Message-ID:
<[hidden email]>
X-Mozilla-Draft-Info:
internal/draft; vcard=0; receipt=0; DSN=0; uuencode=0; attachmentreminder=0
Agente de usuario::
Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:38.0) Gecko/20100101 Thunderbird/38.7.1
MIME-Version:
1.0
In-Reply-To:
<[hidden email]>
Content-Type:
multipart/related; boundary="------------090000020204070106050507"

No problem on translating... but can explain what it does very easily....

If you try to do a PUT or a DELETE on my Davical server :

1 - It's checked if a file called as the own username in Davical, does not exist in a folder. It creates it in this case, and with the PID of the creator proccess inside the plain text file. This write down
of the PID is done with exclusive write lock.

2.- If a file called as the own username in Davical does exist in a folder, it's content is read to see which PID created it (which pid is written inside).

3.- If the pid of the reading proccess and written inside the pid are the same... the proccess goes on...

4.- If the pid of the reading proccess and written inside the pid are not the same... the proccess returns to point 2 and gets looped in the 2,3 and 4 steps till step 3 succeeds.

5.- When PID read from file and the own proccess's one are the same, and all tasks (all code instructions) have been completed, this token filename (whose name is the username) is removed from the directory.

This way I avoided deadlocks....

That's how it works...

Bye Marten!!


El 4/4/16 a las 22:44, Marten Gajda escribió:
Hi Egoitz,

would you mind translating the comments and messages to English? That
would make it easier to understand how it works when reviewing the patch.

cheers

Marten

Am 03.04.2016 um 22:35 schrieb Egoitz Aurrekoetxea:
Sorry, for applying it as you would easily see.. you can copy it to
inc directory of Davical 1.1.4 and do a patch -p0 there...

El 3/4/16 a las 22:31, Egoitz Aurrekoetxea escribió:

    

--


<img src="imap://egoitz%40sarenet%2Ees@correo.sarenet.es:993/fetch%3EUID%3E/Drafts%3E25927?newlogo&part=1.2" alt="sarenet">
Egoitz Aurrekoetxea
Departamento de sistemas
944 209 470
Parque Tecnológico. Edificio 103
48170 Zamudio (Bizkaia)

Antes de imprimir este correo electrónico piense si es necesario hacerlo.

------------------------------------------------------------------------------

_______________________________________________
Davical-general mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/davical-general
Loading...