Thread: lock problem when dont commit
Hello I use postgresql 8.0.1 under windows and jdbc3 with new java tiger... i work on a database with manuel commits and rollback.. my problem is when 2 people work in on the same time on the database: ---table liefAdress------------- pseudecode client1: ... conn.setAutoCommit(false) insert into liefAdress(adress,postcode) //id is autoincrement insert into liefAdress(adress2,postcode2) .... client2: insert into liefAdress(adress3,postcode3) --->here is the problem user1 dont commit his transactions.. the client software are locked.. you can do nothing in the programm..!! client1: conn.commit() //all the adresses are inserted client2: the adress is also inserted and the java clientsoftware is unlocked...! my question is why the java programm from client2 is locked??? can I say to the connection "when you must wait for the commit of the other users bring me a warning" or why the row from client2 are not inserted???? I dont lock everything.. must i change the transaction mode?? I use the default transaction mode.. thank you for your answers!!! Mican Bican
There must be something else going on here. There is no lock on an insert. Are you doing a select for update ? Dave Mican Bican wrote: >Hello > >I use postgresql 8.0.1 under windows and jdbc3 with new java tiger... > >i work on a database with manuel commits and rollback.. > >my problem is when 2 people work in on the same time on the database: > >---table liefAdress------------- > >pseudecode > >client1: >... >conn.setAutoCommit(false) >insert into liefAdress(adress,postcode) >//id is autoincrement >insert into liefAdress(adress2,postcode2) >.... > >client2: >insert into liefAdress(adress3,postcode3) > >--->here is the problem user1 dont commit his >transactions.. the client software are locked.. >you can do nothing in the programm..!! > >client1: >conn.commit() >//all the adresses are inserted > >client2: >the adress is also inserted and the >java clientsoftware is unlocked...! > > >my question is why the java programm from client2 is locked??? >can I say to the connection "when you must wait for the commit of the >other users >bring me a warning" or why the row from client2 are not inserted???? I >dont lock everything.. must i change the transaction mode?? I use the >default transaction mode.. > >thank you for your answers!!! > > >Mican Bican > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
Dave Cramer <pg@fastcrypt.com> writes:
> There must be something else going on here. There is no lock on an
> insert. Are you doing a select for update ?
Maybe a conflict on referenced foreign-key rows?
regards, tom lane
Tom, can you explain how that happens? Dave Tom Lane wrote: >Dave Cramer <pg@fastcrypt.com> writes: > > >>There must be something else going on here. There is no lock on an >>insert. Are you doing a select for update ? >> >> > >Maybe a conflict on referenced foreign-key rows? > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
Dave Cramer <pg@fastcrypt.com> writes:
> Tom Lane wrote:
>> Maybe a conflict on referenced foreign-key rows?
> can you explain how that happens?
When you insert or update a row in a table that has an FK reference,
it takes out a row-level lock on the referenced row in the master table.
This is needed to prevent someone else from deleting the master row
before commit. (Since the someone else can't see your uncommitted row,
nothing but a lock could stop them from thinking it's OK to remove the
master row.)
So if two sessions are trying to insert rows that reference the same
master row, they conflict --- because the only type of row-level lock
we have at the moment is exclusive. I believe Alvaro is looking into
supporting shared row-level locks for 8.1, which'd make this problem
go away.
regards, tom lane
Tom Lane schrieb: >Dave Cramer <pg@fastcrypt.com> writes: > > >>Tom Lane wrote: >> >> >>>Maybe a conflict on referenced foreign-key rows? >>> >>> > > > >>can you explain how that happens? >> >> > >When you insert or update a row in a table that has an FK reference, >it takes out a row-level lock on the referenced row in the master table. >This is needed to prevent someone else from deleting the master row >before commit. (Since the someone else can't see your uncommitted row, >nothing but a lock could stop them from thinking it's OK to remove the >master row.) > >So if two sessions are trying to insert rows that reference the same >master row, they conflict --- because the only type of row-level lock >we have at the moment is exclusive. I believe Alvaro is looking into >supporting shared row-level locks for 8.1, which'd make this problem >go away. > > regards, tom lane > > > Ok I think thats the problem.. because client1: ... conn.setAutoCommit(false) insert into liefAdress(adress,postcode) //id is autoincrement insert into liefAdress(adress2,postcode2) .... .... postcode is a FK to a list with postcodes. When I want insert different postcodes they are no locked.. there is only locked when the postcode are same. But why there are locked? Have anybody a good Idea to deal with this problem..?
As Tom pointed out this behaviour will not change in the immediate future. I think your best bet is not to hold the lock for so long. I generally prepare all the data and do the insert in one statement. Dave Mican Bican wrote: > Tom Lane schrieb: > >> Dave Cramer <pg@fastcrypt.com> writes: >> >> >>> Tom Lane wrote: >>> >>> >>>> Maybe a conflict on referenced foreign-key rows? >>>> >>> >> >> >> >>> can you explain how that happens? >>> >> >> >> When you insert or update a row in a table that has an FK reference, >> it takes out a row-level lock on the referenced row in the master table. >> This is needed to prevent someone else from deleting the master row >> before commit. (Since the someone else can't see your uncommitted row, >> nothing but a lock could stop them from thinking it's OK to remove the >> master row.) >> >> So if two sessions are trying to insert rows that reference the same >> master row, they conflict --- because the only type of row-level lock >> we have at the moment is exclusive. I believe Alvaro is looking into >> supporting shared row-level locks for 8.1, which'd make this problem >> go away. >> >> regards, tom lane >> >> >> > Ok I think thats the problem.. because > > client1: > > ... > conn.setAutoCommit(false) > insert into liefAdress(adress,postcode) //id is autoincrement insert > into liefAdress(adress2,postcode2) > .... > .... > > > postcode is a FK to a list with postcodes. When I want insert different > postcodes they are no locked.. there is only locked when the postcode are > same. But why there are locked? Have anybody a good > Idea to deal with this problem..? > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561