Results 1 to 14 of 14

Thread: Locking a table

  1. #1
    Join Date
    May 2007
    Posts
    24

    Unanswered: Locking a table

    Not quite sure if locking down a table is the correct path to do this.

    I have a customer table, and I want to create a new customer.
    and it will return the customer id, which is the primary key with a sequence.

    public static int createNewCustomer(....)

    //create the new customer
    .....
    .....
    //run a query on the Max(customer_id) to get the just made customer
    ......


    Would I need to lock the table down, just in case two different users tries to create a new Customer at the exact same time.

    Currently using PostgreSQl. Not sure how to lock a table down yet, but just wondering if this would be the correct method to do this.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to postgresql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    24
    I read that locking tables should be avoided, so I am not sure how to go about this.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're right, locking should be avoided, so, since locking is necessary if you use the "select max(pk)+1" technique, therefore you shouldn't use that technique



    if i were a postgresql user, i would use a sequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Use a sequence to generate the PK, before closing the connection, just issue a "Select SequenceName.CurrVal" to get the current value of the sequence. Even if another use adds a record between the time that you create your record and the time that you request the currVal, YOUR instance of the sequence will not be affected at all. Sequences are assigned in isolation of other connections. In other words, the sequences that YOU get are local to YOUR connection. You will get NO interference from any other user, guaranteed. Therefore, No table lock is ever needed for this reason, ever. And, with PostgreSQL's multi-version concurrency control (MVCC,) table locks aren't needed, anyway.

    Now, depending on the manner in which you are inserting records, you may need to adjust your sequence properties. (If you are assigning large blocks of numbers at a time, you should have your sequence reserve blocks of numbers for you. This is the cache sequence setting. However, if you only assign a few at a time, leave the cache setting at 1, so that you won't have large blocks of unassigned (and never assignable) sequence numbers. I've adjusted sequence cache settings up (for efficiency) when performing bulk inserts, and then reset it to a low value when I go back to "normal" operations.

    BTW. In PostgreSQL, If you assign a field to be a SERIAL data type, behind the scenes, postgresql automatically creates a sequence and sets the default value of the field to be the sequence.NextVal. Of course, if you wish, you can also create a sequence manually.
    Last edited by loquin; 06-07-07 at 21:03.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, SERIAL

    yes, that sounds better than SEQUENCE

    thanks lou

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2007
    Posts
    24
    Quote Originally Posted by loquin
    "Select SequenceName.CurrVal"

    BTW. In PostgreSQL, If you assign a field to be a SERIAL data type, behind the scenes, postgresql automatically creates a sequence and sets the default value of the field to be the sequence.NextVal. Of course, if you wish, you can also create a sequence manually.
    Thx, ill try it out now, quite understanding the SERIAL data type part though

  8. #8
    Join Date
    May 2007
    Posts
    24
    Can you clarify on how to get the sequence current value?

    I created a sequence using pgadminIII.
    Created a new column of type Integer, and set the default value to: nextval('sequencename'::regclass)

    When I tried SELECT sequencename.CurrVal it didnt work.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CrazyTn
    When I tried SELECT sequencename.CurrVal it didnt work.
    That is Oracle syntax.

    A quick look into the Postgres manual shows that you need to use a function to retrieve the values:

    select currval('sequencename')

  10. #10
    Join Date
    May 2007
    Posts
    24
    Thanks,
    The way my program runs right now, once it starts it will keep the connection to the database until it closes.

    Is it better to only connect and close the connection when I need to?
    Last edited by CrazyTn; 06-08-07 at 11:18.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CrazyTn
    I was also wondering if it is necessary to close your PreparedStatements, ResultSets in java.
    Yes, absolutely. Especially closing the ResultSet will release the server side cursors and other resources.
    And it is good coding style as well. Thus you make it clear when you don't need the objects anyl longer.

  12. #12
    Join Date
    May 2007
    Posts
    24
    More confusion

    Each session is every time you connect to the database?
    Does that mean I should close the Connection to the database once I am done with the query or update? And connect again when I need to.

    ERROR: currval of sequence "sequenceName" is not yet defined in this session
    SQL state: 55000

    does this error mean I have to do

    SET [SESSION | LOCAL ] SESSION AUTHORIZATION username

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CrazyTn
    Each session is every time you connect to the database?
    Does that mean I should close the Connection to the database once I am done with the query or update? And connect again when I need to.
    No, you should keep the connection open (or use a connection pool).
    But this is getting way off topic. Questions like that should be posted in the Java forum.
    ERROR: currval of sequence "sequenceName" is not yet defined in this session
    SQL state: 55000
    Did you read the manual?
    Quote Originally Posted by manual
    An error is reported if nextval has never been called for this sequence in this session.

  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Suppose you insert a record into a table which has a sequence used for Field1, and where the default value of the field is the next value of the sequence (default for a serial/longserial type.)

    Code:
    Insert into YourTable (Field 2, Field3) Values ('Value for Field 2', 'Value for Field 3')
    Since the default value for Field1 is the sequence, just ignore it in your insert. As an alternative approach, with PostgreSQL, you may also use the DEFAULT keyword:
    Code:
    Insert into YourTable (Field1, Field 2, Field3) Values (Default, 'Value for Field 2', 'Value for Field 3')
    However, IF you use a standalone sequence, and don't specify a default value for your PK, you need to explicitly use the sequence values when inserting.
    Code:
    Insert into YourTable (Field1, Field 2, Field3) Values (CurrVal('YourSequenceName'),'Value for Field 2', 'Value for Field 3')
    In either case, to get the value used by the insert, look at the current value of the sequence from the same session (connection) that inserted the record, AFTER you insert the record, and before you close the connection, or get another new value from the sequence.
    Code:
    Select nextval('YourSequenceName')

    Quote Originally Posted by shammat
    That is Oracle syntax...
    (yup - the earlier reply WAS the Oracle syntax - I've had my head buried in Oracle the past two weeks...Sorry 'bout that)
    Last edited by loquin; 06-13-07 at 18:16.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •