[firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
Hi guys, I'm looking for some ideas how to find what is causing this lock. I have 2 production databases that process multiple insert/update statements. There is one background process that does all these modifications 24h/day. The problem is that with one of these databases almost every morning when client tries to connect it runs series of inserts in one table. And during that process the insert is blocked by something. The other database experiences similar problem but there the lock is on another table that as I said earlier modified trough the whole day. If such a lock happens it locks all similar requests to these tables. I need to shutdown the main process and run Sweep in order to remove the leftover from all transactions executed after initially locked statement transaction. So I'm looking for ideas how to diagnose this problem? what I can do to pinpoint the source of the problem? I'm using Firebird 2.1.6 Classic on Linux. I have same databases on other production setups that does not have this problem. The difference there is that they don't have that background process running on these setups. Thanks in advance
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
Hi, Hi guys, I'm looking for some ideas how to find what is causing this lock. I have 2 production databases that process multiple insert/update statements. There is one background process that does all these modifications 24h/day. The problem is that with one of these databases almost every morning when client tries to connect it runs series of inserts in one table. And during that process the insert is blocked by something. The other database experiences similar problem but there the lock is on another table that as I said earlier modified trough the whole day. If such a lock happens it locks all similar requests to these tables. I need to shutdown the main process and run Sweep in order to remove the leftover from all transactions executed after initially locked statement transaction. So I'm looking for ideas how to diagnose this problem? what I can do to pinpoint the source of the problem? I'm using Firebird 2.1.6 Classic on Linux. I have same databases on other production setups that does not have this problem. The difference there is that they don't have that background process running on these setups. I would check your transaction settings for both, the non-background process requests and the background task. E.g. WAIT vs. NO WAIT, read requests using READ COMMITTED isolation level with NO REC VERSION etc. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
[firebird-support] Re: Where is changeMultiConnectMode.sh on FreeBSD?
SuperClassic is better if you have multiple cpus and ram http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg25-appx-architectures.html http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg25-appx-architectures.html
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
They all use READ COMMITTED isolation. It is an application that runs in JBoss App server so I have single datasource defined that uses READ COMMITTED. Blocked statements are insert and update. Selects are not blocked.
[firebird-support] Re: Where is changeMultiConnectMode.sh on FreeBSD?
Its a single processor server. Also I like the 'shared cache' of SuperSever.
[firebird-support] Re: Where is changeMultiConnectMode.sh on FreeBSD?
If you look in the firebird port source it can be changed from /etc/rc.conf firebird_mode =superserver https://github.com/freebsd/freebsd-ports/blob/master/databases/firebird25-server/files/firebird.in https://github.com/freebsd/freebsd-ports/blob/master/databases/firebird25-server/files/firebird.in
[firebird-support] Re: Where is changeMultiConnectMode.sh on FreeBSD?
Here is the doc https://raw.githubusercontent.com/freebsd/freebsd-ports/master/databases/firebird25-server/files/pkg-message.in https://raw.githubusercontent.com/freebsd/freebsd-ports/master/databases/firebird25-server/files/pkg-message.in
RE: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
They all use READ COMMITTED isolation. It is an application that runs in JBoss App server so I have single datasource defined that uses READ COMMITTED. Blocked statements are insert and update. Selects are not blocked. Why are inserts blocked? That should not happen unless you a) don’t use generators to fill values into your primary keys or b) have a unique constraint or similar (like triggers limiting the number of child records per master record) I can understand why UPDATEs (including the UPDATE OR INSERT statement) result in lock conflicts and that can be unavoidable and something you have to try to minimize rather than eliminate. INSERTs, on the other hand, should only cause lock conflicts if your business logic determines it should be so. Maybe you could show us how these lock conflicts occur (what does a typical insert statement that causes a lock conflict look like and are there any constraints/unique requirements to any of the fields?) Set
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
On 4-9-2014 13:53, doyc...@dsoft-bg.com [firebird-support] wrote: They all use READ COMMITTED isolation. Are you using the default read committed config of Jaybird, or have you overridden it? Default is isc_tpb_read_committed,isc_tpb_rec_version,isc_tpb_write,isc_tpb_wait It is an application that runs in JBoss App server so I have single datasource defined that uses READ COMMITTED. Blocked statements are insert and update. Selects are not blocked. Is the background process also running on JBoss and using read committed? Mark -- Mark Rotteveel
RE: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
This is the definition of the table that locks on insert: CREATE TABLE CASH_CASHBOX_DAY_AMMOUNT ( CASHBOXID INTEGER NOT NULL, DATE_TIME NUMERIC( 18, 0) NOT NULL, AMMOUNTNUMERIC( 18, 0), CONSTRAINT PK_CASH_CASHBOX_DAY_AMMOUNT PRIMARY KEY (CASHBOXID, DATE_TIME) ); ALTER TABLE CASH_CASHBOX_DAY_AMMOUNT ADD CONSTRAINT FK_CASH_CASHBOX_DAY_AMMOUNT FOREIGN KEY (CASHBOXID) REFERENCES CASH_CASHBOX (ID); It is simple insert that just inserts single record. There is no triggers or generators. Primary key value is know before insert is executed.
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
Yes background process is running to on same application server and uses same data source. I use EJB 2 and CPM for doing database operations. App server is 4.2.3 and jaybird is 2.1.6
