Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
On 18-9-2014 10:14, doyc...@dsoft-bg.com [firebird-support] wrote: here is a stack trace from Java app server. After that you will find back trace from some of fb_inet processes and a list of all live processes. The mssing back traces are the same like for PID Attaching to process 16275. After that is the output of fb_lock_print and then is the output from monitoring tables for all running queries. Well, the 'good' news is that I don't see anything strange in the Java stacktrace. All Firebird connections are waiting for a response from the server. I don't know enough about reading below The Firebird stacktraces, but it looks like apart for process 14830 that they are all waiting for a lock. Attaching to process 14830 #0 0x7fb6da7905b3 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:82 #1 0x7fb6db7eace1 in packet_receive (port=0x7fb6dba93080, buffer=0x7fb6dba93222 , buffer_length=8192, length=0x7fff4400f60e) at ../src/remote/inet.cpp:3689 #2 0x7fb6db7eaac1 in inet_read (xdrs=0x7fb6dba93148) at ../src/remote/inet.cpp:3369 #3 0x7fb6db7ea856 in inet_getbytes (xdrs=0x7fb6dba93148, buff=0x7fff4400f674 , count=0) at ../src/remote/inet.cpp:3112 #4 0x7fb6db7ea89f in inet_getlong (xdrs=0x1, lp=0x7fff4400f694) at ../src/remote/inet.cpp:3134 #5 0x7fb6db7fff0e in xdr_enum (xdrs=0x1, ip=0x7fff4400f760) at ../src/remote/xdr.cpp:433 #6 0x7fb6db7fd277 in xdr_protocol (xdrs=0x7fb6dba93148, p=0x7fff4400f760) at ../src/remote/protocol.cpp:299 #7 0x7fb6db7ede1d in receive (main_port=0x7fb6dba93080, packet=0x7fff4400f760) at ../src/remote/inet.cpp:2436 #8 0x7fb6db7ff90a in rem_port::receive (this=0xfdfe, pckt=0x7fff4400f0c0) at ../src/remote/remote.cpp:780 #9 0x7fb6db80f820 in SRVR_main (main_port=0x7fb6dba93080, flags=61632) at ../src/remote/server.cpp:290 #10 0x7fb6db80f2ee in server_main (argc=1, argv=0x7fff44010380) at ../src/remote/inet_server.cpp:450 #11 0x00400709 in main (argc=1, argv=0x7fff4400f0c0) at ../src/remote/server_stub.cpp:12 Attaching to process 16275 #0 0x7f1c3c7ec187 in semop () at ../sysdeps/unix/syscall-template.S:82 #1 0x7f1c3d678dee in ISC_event_wait (count=1, events=0x7fffe206c690, values=0x7fffe206c69c, micro_seconds=1000, timeout_handler= 0x7f1c3d83a510 lock_alarm_handler(void*), handler_arg=0x7f1c3bd19e04) at ../src/jrd/isc_sync.cpp:4583 #2 0x7f1c3d83bcad in wait_for_request (request=0x7f1c3b587d78, lck_wait=1, status_vector=0x7f1c3bd3) at ../src/lock/lock.cpp:5059 #3 0x7f1c3d83a28e in grant_or_que (request=0x7f1c3b587d78, lock=0x43fd78, lck_wait=1) at ../src/lock/lock.cpp:3006 #4 0x7f1c3d83775a in LOCK_enq (prior_request=995655032, parent_request=994547264, series=4, value=0x7fffe206c8f0 r\267\b\001, length=4, type=3 '\003', ast_routine=0xfffc, ast_argument=0xfffc, data=0, lck_wait=1, status_vector=0x7fffe206ca50, owner_offset=991199232) at ../src/lock/lock.cpp:567 #5 0x7f1c3d773801 in LCK_lock (tdbb=0x0, lock=0x7fffe206c860, level=3, wait=1) at ../src/jrd/lck.cpp:1084 #6 0x7f1c3d773900 in LCK_lock_non_blocking (tdbb=0x218005, lock=0x7fffe206c860, level=3, wait=1) at ../src/jrd/lck.cpp:810 #7 0x7f1c3d7c3605 in TRA_wait (tdbb=0x7fffe206f320, trans=0x7f1c3b98ec48, number=17348466, wait=jrd_tra::tra_wait) at ../src/jrd/tra.cpp:2099 #8 0x7f1c3d7cc071 in VIO_get_current (tdbb=0x7fffe206f320, old_rpb=0x7fffe206c3b0, rpb=0x7fffe206cce0, transaction=0x7f1c3b98ec48, pool=0x7f1c3b98e1a0, foreign_key=114, has_old_values=@0xfffc) at ../src/jrd/vio.cpp:1941 #9 0x7f1c3d74a041 in check_duplicates (tdbb=0x7fffe206f320, record=0x7f1c3b98c8c0, record_idx=0x7fffe206df70, insertion=0x7fffe206df20, relation_2=0x0) at ../src/jrd/jrd.h:905 #10 0x7f1c3d749cbe in insert_key (tdbb=0x7fffe206f320, relation=0x7f1c3ba95b58, record=0x7f1c3b98c8c0, transaction=0x7f1c3b98ec48, window_ptr=0x7fffe206def0, insertion=0x7fffe206df20, bad_relation=0x7fffe206dea0, bad_index=0xfffc) at ../src/jrd/idx.cpp:1650 #11 0x7f1c3d74907a in IDX_store (tdbb=0x7fffe206f320, rpb=0x7f1c3b98d6a8, transaction=0x7f1c3b98ec48) at ../src/jrd/idx.cpp:1062 #12 0x7f1c3d73a498 in store (tdbb=0x7fffe206f320, node=0x7f1c3b9a7328, which_trig=0) at ../src/jrd/exe.cpp:3685 #13 0x7f1c3d738295 in looper (tdbb=0x7fffe206f320, request=0x7f1c3b98d278, in_node=0x7f1c3d738268) at ../src/jrd/exe.cpp:2635 #14 0x7f1c3d737016 in execute_looper (tdbb=0x7fffe206f320, request=0x7f1c3b98d278, transaction=0x7f1c3b98ec48, next_state=jrd_req::req_proceed) at ../src/jrd/exe.cpp:1443 #15 0x7f1c3d7367a0 in EXE_send (tdbb=0x7fffe206f320, request=0x7f1c3b98d278, msg=6, length=80, buffer=0x7f1c3b9a1f78 \t) at ../src/jrd/exe.cpp:993 #16 0x7f1c3d76ad04 in jrd8_start_and_send (user_status=0x7fffe206fa40, req_handle=0x7f1c3b98d278,
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
On 5-9-2014 19:48, doyc...@dsoft-bg.com [firebird-support] wrote: I'm not sure it is locked table. What I'm looking for is idea or advice how to find what the real problem is. I'd start with obtaining a thread dump of the application when it is hanging. The stacktraces might hint at the cause. Also if you are using Firebird 2.5, use the trace facility for logging the activity. Also consider upgrading to Jaybird 2.2.5; I fixed several concurrency bugs which could cause livelock or deadlock (although most of those were in parts you aren't using). Other than that: investigate your code to make sure it isn't using long running transactions. My data source definition is this: connection-factories tx-connection-factory jndi-namePowerProDS/jndi-name xa-transaction/ track-connection-by-tx/ rar-namejaybird-2.1.6.rar/rar-name connection-definitionjavax.sql.DataSource/connection-definition config-property name=Database type=java.lang.Stringlocalhost/3050:powerpro/config-property user-namesysdba/user-name passwordmasterkey/password config-property name=EncodingUNICODE_FSS/config-property ; prepared-statement-cache-size100/prepared-statement-cache-size min-pool-size10/min-pool-size max-pool-size50/max-pool-size metadata type-mappingEuroPro/type-mapping /metadata /tx-connection-factory /connection-factories -- 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
I'm using Firebird 2.1.6. Also I tried to upgrade to latest jaybird before but it does not work properly with JBoss 4.2.3. I remember something has changed and as soon as I try to start apps server and run client, exceptions were starting to popup in the log. This code path uses long running transaction only if for some reason the history for a cash box is lost and has to be rebuild day by day. This is not the case in the moment. usually the execution of this operation have to take not more then just a second or a little more when more then 5-6 cash boxes has to be recalculated.
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
On 04 Sep 2014 13:31:14 -0700, doyc...@dsoft-bg.com [firebird-support] firebird-support@yahoogroups.com wrote: 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 How have you determined the problem has to do with locked tables (which takes some effort in Firebird), and not with the connection (or obtaining the connection from the datasource)? What is your datasource configuration and which datasource class do you use? Mark
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. I see two likely ways an insert can fail here: 1) The CASHBOXID doesn’t exist or has been inserted in another transaction that is not yet committed (possible solution: insert into CASH_CASHBOX and CASH_CASHBOX_DAY_AMMOUNT in the same transaction) 2) A record with the same CASHBOXID and DATE_TIME already exists Can be avoided by using a new field populated through a generator as the primary key – but you would then probably have to also implement a ‘duplicate resolution’-button or similar in your program to fix up those cases where there are several records with the same CASHBOXID and DATE_TIME Either of these ought to give you an error message (which would be good to post on this list). Your problem could of course be related to something completely different (like Mark suggests – or something stupid like the user having no insert rights to the table), but if it is the inserts themselves that are the problem, I see no other possible reason for a lock conflict. Set
Re: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
I'm not sure it is locked table. What I'm looking for is idea or advice how to find what the real problem is. My data source definition is this: connection-factories tx-connection-factory jndi-namePowerProDS/jndi-name xa-transaction/ track-connection-by-tx/ rar-namejaybird-2.1.6.rar/rar-name connection-definitionjavax.sql.DataSource/connection-definition config-property name=Database type=java.lang.Stringlocalhost/3050:powerpro/config-property user-namesysdba/user-name passwordmasterkey/password config-property name=EncodingUNICODE_FSS/config-property prepared-statement-cache-size100/prepared-statement-cache-size min-pool-size10/min-pool-size max-pool-size50/max-pool-size metadata type-mappingEuroPro/type-mapping /metadata /tx-connection-factory /connection-factories
[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.
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.
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
