Derby Locks - best practices
Hello, all: Derby is used in my application in the in-memory only mode. For a long time Derby's lock logic caused no worries, but recently some use cases failed with lock timeouts. Thus I'm looking for guidance on best practices for handling locks in Derby. A use-case which may cause timeouts to obtain a lock: one thread is executing an SQL statement which accesses two (or more) in-memory tables. Those two tables are being modified by other threads at random times. So, situations in which the SQL is executed for a long time and the other threads are frequently updating the tables may cause lock timeouts. Besides best practices to avoid timeouts and deadlocks, I would like to ask the following questions: 1) What's the default length of lock timeouts? 2) Does my app need another layer of synchronization mechanism/locks to avoid attempts to update in-memory tables or execute SQLs against them? 3) Can my application utilize Derby's locks through some API - to query their state or to use them in making a decision of whether to batch updates or to execute them, to wait or execute the SQLs? Your help would be greatly appreciated, Pavel. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Derby Locks - best practices
Do mean the scenario of: Multiple threads are updating the exact same rows or Multiple threads doing updates to different rows, but due to queries/indexes/etc are causing contention between each other. On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiy pbortnovs...@jefferies.com wrote: Hello, all: Derby is used in my application in the in-memory only mode. For a long time Derby’s lock logic caused no worries, but recently some use cases failed with lock timeouts. Thus I’m looking for guidance on best practices for handling locks in Derby. A use-case which may cause timeouts to obtain a lock: one thread is executing an SQL statement which accesses two (or more) in-memory tables. Those two tables are being modified by other threads at random times. So, situations in which the SQL is executed for a long time and the other threads are frequently updating the tables may cause lock timeouts. Besides best practices to avoid timeouts and deadlocks, I would like to ask the following questions: 1) What’s the default length of lock timeouts? 2) Does my app need another layer of synchronization mechanism/locks to avoid attempts to update in-memory tables or execute SQLs against them? 3) Can my application utilize Derby’s locks through some API – to query their state or to use them in making a decision of whether to batch updates or to execute them, to wait or execute the SQLs? Your help would be greatly appreciated, Pavel. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
RE: Derby Locks - best practices
Hello, David, thanks for your quick response. Usually it's one thread per in-memory table. Tables can be updated at random times and their random rows may be updated, some rows deleted or new rows inserted. In some other configuration, to avoid deletions, updates and inserts, the in-memory table is truncated and then all the records (the new state of the source data) are inserted into it. The thread which runs SQL against all those tables frequently may do a scan of the whole table. -Original Message- From: David Zanter [mailto:dzan...@gmail.com] Sent: Friday, June 01, 2012 3:46 PM To: Derby Discussion Subject: Re: Derby Locks - best practices Do mean the scenario of: Multiple threads are updating the exact same rows or Multiple threads doing updates to different rows, but due to queries/indexes/etc are causing contention between each other. On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiy pbortnovs...@jefferies.com wrote: Hello, all: Derby is used in my application in the in-memory only mode. For a long time Derby's lock logic caused no worries, but recently some use cases failed with lock timeouts. Thus I'm looking for guidance on best practices for handling locks in Derby. A use-case which may cause timeouts to obtain a lock: one thread is executing an SQL statement which accesses two (or more) in-memory tables. Those two tables are being modified by other threads at random times. So, situations in which the SQL is executed for a long time and the other threads are frequently updating the tables may cause lock timeouts. Besides best practices to avoid timeouts and deadlocks, I would like to ask the following questions: 1) What's the default length of lock timeouts? 2) Does my app need another layer of synchronization mechanism/locks to avoid attempts to update in-memory tables or execute SQLs against them? 3) Can my application utilize Derby's locks through some API - to query their state or to use them in making a decision of whether to batch updates or to execute them, to wait or execute the SQLs? Your help would be greatly appreciated, Pavel. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Derby Locks - best practices
On 01.06.2012 21:50, Pavel Bortnovskiy wrote: Hello, David, thanks for your quick response. Usually it's one thread per in-memory table. Tables can be updated at random times and their random rows may be updated, some rows deleted or new rows inserted. In some other configuration, to avoid deletions, updates and inserts, the in-memory table is truncated and then all the records (the new state of the source data) are inserted into it. For clarity, are you referring to using Derby's memory subprotocol when talking about in-memory tables? As an example, that would be 'jdbc:derby:memory:mydb;create=true', as opposed to the on-disk version that would be 'jdbc:derby:mydb;create=true'. In terms of locking there is nothing special about in-memory database in Derby, except for the likely event that some operations may be faster in-memory than on-disk (which could affect timing, but many other things can do that too). Two common pieces of advice when it comes to locking is to reduce the duration of the locks, and to reduce the scope/granularity of the locks. There may also be application specific considerations to take, like acceptable isolation levels, access patterns and schema design. In general your application should be prepared to handle lock timeouts, whereas deadlocks indicate that the access/lock patterns of your application need to be improved. -- Kristian The thread which runs SQL against all those tables frequently may do a scan of the whole table. -Original Message- From: David Zanter [mailto:dzan...@gmail.com] Sent: Friday, June 01, 2012 3:46 PM To: Derby Discussion Subject: Re: Derby Locks - best practices Do mean the scenario of: Multiple threads are updating the exact same rows or Multiple threads doing updates to different rows, but due to queries/indexes/etc are causing contention between each other. On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiypbortnovs...@jefferies.com wrote: Hello, all: Derby is used in my application in the in-memory only mode. For a long time Derby's lock logic caused no worries, but recently some use cases failed with lock timeouts. Thus I'm looking for guidance on best practices for handling locks in Derby. A use-case which may cause timeouts to obtain a lock: one thread is executing an SQL statement which accesses two (or more) in-memory tables. Those two tables are being modified by other threads at random times. So, situations in which the SQL is executed for a long time and the other threads are frequently updating the tables may cause lock timeouts. Besides best practices to avoid timeouts and deadlocks, I would like to ask the following questions: 1) What's the default length of lock timeouts? 2) Does my app need another layer of synchronization mechanism/locks to avoid attempts to update in-memory tables or execute SQLs against them? 3) Can my application utilize Derby's locks through some API - to query their state or to use them in making a decision of whether to batch updates or to execute them, to wait or execute the SQLs? Your help would be greatly appreciated, Pavel. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
RE: Derby Locks - best practices
Kristian, what are the lengths of time for default timeouts? Can you please either list them or point to where in the code they may be defined? Yes, when I say in-memory, I refer to it as memory subprotocol: jdbc:derby:memory:test;create=true -Original Message- From: Kristian Waagan [mailto:kristian.waa...@oracle.com] Sent: Friday, June 01, 2012 6:12 PM To: derby-user@db.apache.org Subject: Re: Derby Locks - best practices On 01.06.2012 21:50, Pavel Bortnovskiy wrote: Hello, David, thanks for your quick response. Usually it's one thread per in-memory table. Tables can be updated at random times and their random rows may be updated, some rows deleted or new rows inserted. In some other configuration, to avoid deletions, updates and inserts, the in-memory table is truncated and then all the records (the new state of the source data) are inserted into it. For clarity, are you referring to using Derby's memory subprotocol when talking about in-memory tables? As an example, that would be 'jdbc:derby:memory:mydb;create=true', as opposed to the on-disk version that would be 'jdbc:derby:mydb;create=true'. In terms of locking there is nothing special about in-memory database in Derby, except for the likely event that some operations may be faster in-memory than on-disk (which could affect timing, but many other things can do that too). Two common pieces of advice when it comes to locking is to reduce the duration of the locks, and to reduce the scope/granularity of the locks. There may also be application specific considerations to take, like acceptable isolation levels, access patterns and schema design. In general your application should be prepared to handle lock timeouts, whereas deadlocks indicate that the access/lock patterns of your application need to be improved. -- Kristian The thread which runs SQL against all those tables frequently may do a scan of the whole table. -Original Message- From: David Zanter [mailto:dzan...@gmail.com] Sent: Friday, June 01, 2012 3:46 PM To: Derby Discussion Subject: Re: Derby Locks - best practices Do mean the scenario of: Multiple threads are updating the exact same rows or Multiple threads doing updates to different rows, but due to queries/indexes/etc are causing contention between each other. On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiypbortnovs...@jefferies.com wrote: Hello, all: Derby is used in my application in the in-memory only mode. For a long time Derby's lock logic caused no worries, but recently some use cases failed with lock timeouts. Thus I'm looking for guidance on best practices for handling locks in Derby. A use-case which may cause timeouts to obtain a lock: one thread is executing an SQL statement which accesses two (or more) in-memory tables. Those two tables are being modified by other threads at random times. So, situations in which the SQL is executed for a long time and the other threads are frequently updating the tables may cause lock timeouts. Besides best practices to avoid timeouts and deadlocks, I would like to ask the following questions: 1) What's the default length of lock timeouts? 2) Does my app need another layer of synchronization mechanism/locks to avoid attempts to update in-memory tables or execute SQLs against them? 3) Can my application utilize Derby's locks through some API - to query their state or to use them in making a decision of whether to batch updates or to execute them, to wait or execute the SQLs? Your help would be greatly appreciated, Pavel. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority. Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts
Re: Derby Locks - best practices
Pavel Bortnovskiy pbortnovs...@jefferies.com writes: Besides best practices to avoid timeouts and deadlocks, I would like to ask the following questions: 1) What's the default length of lock timeouts? According to http://db.apache.org/derby/docs/10.8/ref/rrefproper10607.html derby.locks.deadlockTimeout default is 20 seconds. This setting should be seen in conjunction with that of derby.locks.waitTimeout (usually deadlockTimeout is lower), cf advice here: http://db.apache.org/derby/docs/10.8/devguide/cdevconcepts16400.html
Re: Derby Locks - best practices
Pavel Bortnovskiy pbortnovs...@jefferies.com writes: 1) What's the default length of lock timeouts? 60 seconds. cf. http://db.apache.org/derby/docs/10.8/ref/rrefproper46141.html
RE: Derby Locks - best practices
Thank you, Dag. So, what may be my best strategy in the described situation? Let me recap a case like such: Say Derby is running in in-memory subprotocol. There are two tables and three threads. Two threads perform inserts/deletes/updates and merges (implemented as update+insert) on each corresponding table. And the third thread runs an SQL on those two tables. I forgot to mention a few details: - the threads perform table updates in a batched way: Prepared statements are created and then they are batched. At a certain the batches are executed - the SQL is very slow running - for argument sake, it may take 3-4 mins to run it - much longer than a lock timeout. What would be the best way to assure proper functioning of the app and avoid timeouts? It has been suggested to repeat a transaction if a lock time out exception is caught, but that would mean to execute the whole batch again... Thank you for your help, Pavel. -Original Message- From: Dag H. Wanvik [mailto:dag.wan...@oracle.com] Sent: Friday, June 01, 2012 7:30 PM To: Derby Discussion Subject: Re: Derby Locks - best practices Pavel Bortnovskiy pbortnovs...@jefferies.com writes: 1) What's the default length of lock timeouts? 60 seconds. cf. http://db.apache.org/derby/docs/10.8/ref/rrefproper46141.html Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Derby Locks - best practices
On 6/1/2012 4:50 PM, Pavel Bortnovskiy wrote: Thank you, Dag. So, what may be my best strategy in the described situation? Let me recap a case like such: Say Derby is running in in-memory subprotocol. There are two tables and three threads. Two threads perform inserts/deletes/updates and merges (implemented as update+insert) on each corresponding table. And the third thread runs an SQL on those two tables. I forgot to mention a few details: - the threads perform table updates in a batched way: Prepared statements are created and then they are batched. At a certain the batches are executed - the SQL is very slow running - for argument sake, it may take 3-4 mins to run it - much longer than a lock timeout. What would be the best way to assure proper functioning of the app and avoid timeouts? It has been suggested to repeat a transaction if a lock time out exception is caught, but that would mean to execute the whole batch again... You can set the timeout to be longer if that is acceptable to wait http://db.apache.org/derby/docs/10.8/ref/rrefproper46141.html
