Thread: How to Force Transactions to Process Serially on A Table
I am using PL/SQL functions on Windows 2003, version 8.0.1. I have not used explicit PostgreSQL LOCKing before, but I need some advice on how properly to use some explicit locking. I think that duplicate key violations I am now getting are the result. I want to force transactions being used to update a table to be processed on a first-come, first-served basis. I want my Pl/sql function to execute to completion on each transaction before another starts. Need some advice on how to do this. From what I can read in the docs, it looks like I need to solve the problem by using the following, but doing so gives me an SPI_execution error: BEGIN; LOCK <table> IN SHARE ROW EXCLUSIVE MODE; lock adns_report_hour_history in sharerow exclusive mode; INSERT INTO <table> VALUES ... - or - UPDATE <table> SET .... COMMIT; Will this make the next transaction wait until the previous transaction has completed? Do I need to set any config parameters? If you can include an actual code snippet in the response, it would help ...
O Lane Van Ingen έγραψε στις Dec 19, 2005 : > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > on how properly to use some explicit locking. I think that duplicate key > violations I am now getting are the result. > > I want to force transactions being used to update a table to be processed on > a first-come, first-served basis. I want my Pl/sql function to execute to > completion on each transaction before another starts. > > Need some advice on how to do this. From what I can read in the docs, it > looks like I need to solve the problem by using the following, but doing so > gives me an SPI_execution error: > BEGIN; > LOCK <table> IN SHARE ROW EXCLUSIVE MODE; > lock adns_report_hour_history in share row exclusive mode; > INSERT INTO <table> VALUES ... - or - UPDATE <table> SET .... > COMMIT; > Will this make the next transaction wait until the previous transaction has > completed? Do I need to set any config parameters? > > If you can include an actual code snippet in the response, it would help ... what you want is to set the xaction isolation level. BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; ...... COMMIT; Be prepared for serialization failures though. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- -Achilleus
Thanks, that helped. Please answer 2 other related questions, if you would: (1) What must I do to 'Be prepared for serialization failures' (how to detect, how to handle)? Do you have a sample? (2) Also, I am assuming that the effect of all of this is to just force transactions to wait in line to be processed serially, and that it only lasts as long as the pl/pgsql transaction block or the next COMMIT. -----Original Message----- From: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com] Sent: Monday, December 19, 2005 9:25 AM To: Lane Van Ingen Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] How to Force Transactions to Process Serially on A Table O Lane Van Ingen έγραψε στις Dec 19, 2005 : > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > on how properly to use some explicit locking. I think that duplicate key > violations I am now getting are the result. > > I want to force transactions being used to update a table to be processed on > a first-come, first-served basis. I want my Pl/sql function to execute to > completion on each transaction before another starts. > > Need some advice on how to do this. From what I can read in the docs, it > looks like I need to solve the problem by using the following, but doing so > gives me an SPI_execution error: > BEGIN; > LOCK <table> IN SHARE ROW EXCLUSIVE MODE; > lock adns_report_hour_history in share row exclusive mode; > INSERT INTO <table> VALUES ... - or - UPDATE <table> SET .... > COMMIT; > Will this make the next transaction wait until the previous transaction has > completed? Do I need to set any config parameters? > > If you can include an actual code snippet in the response, it would help ... what you want is to set the xaction isolation level. BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; ...... COMMIT; Be prepared for serialization failures though. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- -Achilleus
O Lane Van Ingen έγραψε στις Dec 19, 2005 : > Thanks, that helped. > > Please answer 2 other related questions, if you would: > (1) What must I do to 'Be prepared for serialization failures' (how to > detect, how to handle)? > Do you have a sample? Look at http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html > (2) Also, I am assuming that the effect of all of this is to just force > transactions to wait in line > to be processed serially, and that it only lasts as long as the pl/pgsql > transaction block or > the next COMMIT. > Then transaction isolation SERIALIZABLE is not for this task. What you would do is use the SELECT ... FOR UPDATE construct. Normally you would not care about locking the whole table but only row of interest. > -----Original Message----- > From: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com] > Sent: Monday, December 19, 2005 9:25 AM > To: Lane Van Ingen > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] How to Force Transactions to Process Serially on A Table > > O Lane Van Ingen έγραψε στις Dec 19, 2005 : > > > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > > on how properly to use some explicit locking. I think that duplicate key > > violations I am now getting are the result. > > > > I want to force transactions being used to update a table to be processed > on > > a first-come, first-served basis. I want my Pl/sql function to execute to > > completion on each transaction before another starts. > > > > Need some advice on how to do this. From what I can read in the docs, it > > looks like I need to solve the problem by using the following, but doing > so > > gives me an SPI_execution error: > > BEGIN; > > LOCK <table> IN SHARE ROW EXCLUSIVE MODE; > > lock adns_report_hour_history in share row exclusive mode; > > INSERT INTO <table> VALUES ... - or - UPDATE <table> SET .... > > COMMIT; > > Will this make the next transaction wait until the previous transaction > has > > completed? Do I need to set any config parameters? > > > > If you can include an actual code snippet in the response, it would help > ... > > what you want is to set the xaction isolation level. > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > ...... > COMMIT; > > Be prepared for serialization failures though. > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > -Achilleus > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- -Achilleus
On Mon, Dec 19, 2005 at 08:46:39AM -0500, Lane Van Ingen wrote: > Thanks, that helped. > > Please answer 2 other related questions, if you would: > (1) What must I do to 'Be prepared for serialization failures' (how to > detect, how to handle)? > Do you have a sample? You'll get an error. You should read this section of the docs: http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-SERIALIZABLE > (2) Also, I am assuming that the effect of all of this is to just force > transactions to wait in line > to be processed serially, and that it only lasts as long as the pl/pgsql > transaction block or > the next COMMIT. No. The effect is to _emulate_ the case where the set transaction is processed serially. Importantly, on a high-concurrency database, you tend to get serialization failures. Moreover, it is not true mathematical serialization. See section 12.2.2.1 for details in case you need such a feature, in which case you're back to explicit locking. A > > -----Original Message----- > From: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com] > Sent: Monday, December 19, 2005 9:25 AM > To: Lane Van Ingen > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] How to Force Transactions to Process Serially on A Table > > O Lane Van Ingen ?????? ???? Dec 19, 2005 : > > > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > > on how properly to use some explicit locking. I think that duplicate key > > violations I am now getting are the result. > > > > I want to force transactions being used to update a table to be processed > on > > a first-come, first-served basis. I want my Pl/sql function to execute to > > completion on each transaction before another starts. > > > > Need some advice on how to do this. From what I can read in the docs, it > > looks like I need to solve the problem by using the following, but doing > so > > gives me an SPI_execution error: > > BEGIN; > > LOCK <table> IN SHARE ROW EXCLUSIVE MODE; > > lock adns_report_hour_history in share row exclusive mode; > > INSERT INTO <table> VALUES ... - or - UPDATE <table> SET .... > > COMMIT; > > Will this make the next transaction wait until the previous transaction > has > > completed? Do I need to set any config parameters? > > > > If you can include an actual code snippet in the response, it would help > ... > > what you want is to set the xaction isolation level. > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > ...... > COMMIT; > > Be prepared for serialization failures though. > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > -Achilleus > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Andrew Sullivan | ajs@crankycanuck.ca When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes