Commit

[former_member203645]

Hi All,

We have issues with commit to lock and unlock. There many people in the organization who are new to sybase IQ .

So while running any queries they forget to issue commit  at the end of each statement. Due to this if some one tries to access the table then it says table is locked. It's blocking others work.

I know we can see the table lock in Sybase central but some are shared usernames. So it was problem to contact them.

Is there a way to run the queries with out COMMIT and it should not get locked. Even block others work. ????

[saroj_bagai · Accepted solution]

set Autocommit to true in odbc Administrator

[former_member203645 · Accepted solution]

Hi Saroj,

Please let me know where can I find that option, Please find the attachment , I see an option called "Delay AutoCommit until Statement close". Is this option you are discussing about. Do I need check this option.

Also I want makes again, So any developers don't need to issue a commit at end of the statement right.

[saroj_bagai · Accepted solution]

You can handle it in couple of ways:

AutoPreCommit Connection Parameter [AutoPreCommit]

Forces each statement to COMMIT before execution.

Usage

ODBC

Default

NO

Description

By default, statements issue a COMMIT after execution.
When AutoPreCommit = 'yes' commit statements are issued
before each select statement, so that users can always see the latest
version of all database objects.

Example

You can set the AutoPreCommit option to YES (Y) to turn on
commit before execution or NO (N) to turn it off. Set this option
in the .odbc.ini file or on the Advanced tab of the Connect
dialog.

For example, the following causes each statement to COMMIT before execution:

[sample DSN]

DatabaseFile=c:\program files\Sybase\IQ-15_4\demo\iqdemo.db

AutoPreCommit=Y

UserID=DBA

Password=sql

or you can AutoPreCommit='Y' under Advaced Tab of Administrator

2. Check 'Delay Autocommit until Statement close'

3.  From Sybase Central -> Interactive SQL -> options -> Sybase IQ -> Commands-> check 'Commit after every Statement'

[former_member203645 · Accepted solution]

Thanks a lot for understanding me completely. My final question is

How to add the parameter AutoPreCommit=Y ??

Because I don't see an option to add the parameter or I don't know the way to add. Please help me out. I am very new to Sybase IQ.

[harpreet_chaudhary · Accepted solution]

You can add the parameter to the 'Additional connection parameters' section on the Advanced tab in the odbc administrator

[former_member203645 · Accepted solution]

Got it , Awesome.

I am just copy and pasting the AutoPreCommit='Y' in the box.

Thanks a lot for info,

[saroj_bagai · Accepted solution]

in advance tab

AutoPreCommit=Y

[Former Member · Accepted solution]

Hi RUC,

I believe the question has been answered, could you please mark the question as answered, and any of the helpful responses as helpful,

Many thanks

Andrew

[markmumy]

Can you explain more about what the issue is?  If a user is reading a table and only has a READ lock on the table it is impossible for that user to block any other queries (READ locks).  It is also impossible for a READ lock to block any WRITE locks.  It is just as impossible for a WRITE lock to block READ locks.  Your comment about a query blocking other users is quite confusing since this condition cannot happen in IQ with READ and WRITE locks.

[Former Member]

Not with read an write, but two simultaneous writes/updates to a table cause the locking issue. If OP can provide more info that would be helpful.