Re: Transactions - Heikki Tuuri - com.mysql.lists.java

2 messages in com.mysql.lists.javaRe: Transactions

From	Sent On	Attachments
JAKE WATSON	04 Sep 2001 20:44
Heikki Tuuri	05 Sep 2001 05:12

Subject:	Re: Transactions
From:	Heikki Tuuri (Heik...@innobase.inet.fi)
Date:	09/05/2001 05:12:17 AM
List:	com.mysql.lists.java

Jake,

Has anyone been using transactions in the new version of MySQL? We recently switched to InnoDB tables from MyISAM tables, but are still having "issues" ...

I've got the following problem and am wondering the best way to solve it:

We've got two separate processes that perform a select then update (transaction); only one process (a JDBC connection) uses "SELECT ... FOR UPDATE"; the other process is a Perl script that also performs a kind of transaction, but is currently not performing any locks.

The problem is that the updates are being corrupted, presumably since both processes get a handle on the same insert row ...

are you using > 8000 byte rows for which you update the primary key? There is a bad bug there which always corrupts the table. I have fixed it and I am waiting for Monty to release 3.23.42.

What's the best solution to keep a thread from getting a row in between a select & update?

SELECT ... FOR UPDATE is the best way. Note that if you perform a SELECT without FOR UPDATE or LOCK IN SHARE MODE, the the SELECT is a consistent read and will not see the modifications made by uncommitted or later transactions.

If you do not want a reading SELECT to proceed before your other transaction has done its update, you have to use LOCK IN SHARE MODE or FOR UPDATE also in the reading select.

I've read that "Lock Table ..." is not transaction-safe and will automatically commit any active transactions before locking the table.

I recommend using ... FOR UPDATE or ... LOCK IN SHARE MODE. If you use LOCK TABLE you have to acquire all the locks at the start of trx.

I've read that Last_Insert_ID() is not reliable for processes that only have one ODBC connection (our Perl app).

I assume you know that there can be only one transaction per connection? If you need 2 concurrent transactions you need 2 connections.

I think Last_Insert_ID() is on per connection basis.

Is there a setting that will apply pessimistic locking on all the tables in a MySQL DB? Or must we perform the locking ourselves?

No, it is like in Oracle where you explicitly have to specify those SELECTs where you want pessimistic locking.

tia ...

Regards,

Heikki http://www.innodb.com