From: Brad H. <br...@ma...> - 2005-01-04 04:55:30
|
I was evaluating the native Firebird API, and the ODBC implementation, and noticed that when starting a transaction, the ODBC implementation does not support table locking. I was wondering if there was a specific reason why this had not been implemented, or if it was just something that had not been gotten around to yet. I have a couple of ideas for implementation, which I could probably implement myself, and send patches back, but would not want to do so without some support (e.g. assurance that it will make it back into the master CVS assuming my approach is sane ...). My current test implementation with Firebird seems to hit some high-contention areas (where a lot of rollbacks occur) because an exclusive lock on a table cannot be had earlier on, so this implementation could significantly improve performance in those situations. (NOTE, our SQL implementation is very generic, so the 'proper' ways to avoid the contention other than table locking is out of the question to maintain compatibility with Oracle, DB2, MySQL, PostgreSQL, MSSQL, MCSQL [our own in-house written SQL DB], and now FireBird/Interbase ... The only SQL-specific stuff we have are callbacks for locking, and how to begin, rollback, and commit transactions). Granted, I personally have no need for this, as we do not use Firebird ourselves, but I could see it being a drawback for someone wanting to use our product with Firebird, or anyone else migrating from another SQL database that _does_ support table locking. Anyhow, my concepts for implementation were one of two ways. 1) Overload SQLSetConnectAttr() to take a list of table names, and lock types (READ/WRITE), to apply to the transaction, which upon the first statement that comes in would initialize the transaction _with_ the table locks. Also upon a rollback or commit, the stored table names and lock types would be cleared, so the ODBC implementation on the client-side would need to call SQLSetConnectAttr() for each transaction, even if they wish to lock the same table, or tables, again. I notice you seem to take this approach with two-phase-commits (via key 4000). 2) Create a fully dummy SQL statement or statements. Perhaps use the Oracle style table lock statement (which Oracle, DB2, and PostgreSQL all support): LOCK TABLE mytable IN EXCLUSIVE MODE; or LOCK TABLE mytable IN SHARED MODE; Just to give people a familiar feel. Basically when the first non-LOCK TABLE statement comes in, it will officially create the transaction. Any LOCK TABLE after start will fail with an error. (alternatively the MySQL approach allows you to specify multiple tables at once via LOCK TABLES mytable1 WRITE, mytable2 READ; but it is MySQL specific, and MySQL also has an UNLOCK tables command that is required, which would not be used here... so it may cause some confusion) #2 may be more desirable because some users may not have control over the actual ODBC layer, but do over the SQL commands themselves. Any thoughts on this would be appreciated. -Brad |
From: Vladimir T. <pr...@hm...> - 2005-01-04 10:57:59
|
Hi, Brad! I admit, that I not absolutely correctly have understood this theme :-( Idea I have understood, but what rather Firebird? > I was evaluating the native Firebird API, and the ODBC implementation, > and noticed that when starting a transaction, the ODBC implementation > does not support table locking. I was wondering if there was a specific Firebird - it's version engine > reason why this had not been implemented, or if it was just something > that had not been gotten around to yet. I have a couple of ideas for > implementation, which I could probably implement myself, and send > patches back, but would not want to do so without some support (e.g. > assurance that it will make it back into the master CVS assuming > my approach is sane ...). My current test implementation with Firebird Very well! Send me source to praktik @ hm . ukrtel . net I am ready to criticize yours of source :-) > seems to hit some high-contention areas (where a lot of rollbacks occur) > because an exclusive lock on a table cannot be had earlier on, so this > implementation could significantly improve performance in those > situations. (NOTE, our SQL implementation is very generic, so the > 'proper' ways to avoid the contention other than table locking is > out of the question to maintain compatibility with Oracle, DB2, > MySQL, PostgreSQL, MSSQL, MCSQL [our own in-house written SQL DB], > and now FireBird/Interbase ... The only SQL-specific stuff we > have are callbacks for locking, and how to begin, rollback, > and commit transactions). > > Granted, I personally have no need for this, as we do not use Firebird > ourselves, but I could see it being a drawback for someone wanting to > use our product with Firebird, or anyone else migrating from another > SQL database that _does_ support table locking. > > Anyhow, my concepts for implementation were one of two ways. > 1) Overload SQLSetConnectAttr() to take a list of table names, and > lock types (READ/WRITE), to apply to the transaction, which > upon the first statement that comes in would initialize the > transaction _with_ the table locks. > Also upon a rollback or commit, the stored table names and > lock types would be cleared, so the ODBC implementation on > the client-side would need to call SQLSetConnectAttr() for > each transaction, even if they wish to lock the same table, > or tables, again. I notice you seem to take this approach > with two-phase-commits (via key 4000). This one of the bad decisions. Problem: ODBC32.dll - manager ODBC carries out the rigid control of check of an entering code > > 2) Create a fully dummy SQL statement or statements. Perhaps > use the Oracle style table lock statement (which Oracle, DB2, > and PostgreSQL all support): > LOCK TABLE mytable IN EXCLUSIVE MODE; > or > LOCK TABLE mytable IN SHARED MODE; > Just to give people a familiar feel. Basically when the > first non-LOCK TABLE statement comes in, it will officially > create the transaction. Any LOCK TABLE after start will > fail with an error. > (alternatively the MySQL approach allows you to specify > multiple tables at once via > LOCK TABLES mytable1 WRITE, mytable2 READ; > but it is MySQL specific, and MySQL also has an UNLOCK tables > command that is required, which would not be used here... so it > may cause some confusion) > IMHO, is probable this best decision. However I think that you will take for a basis OdbcJdbc v2.0 And be attentive. OdbcJdbc v2.0 - It is the unfinished realization schema one connection --> many different transactions At me the final decision only is formed :-) Probably you very much in time have written this message, and after viewing yours of source at me the ideas will appear. Thanks! -- Best regards, Vladimir Tsvigun |