# MysqlTransaction

## Function

MySQL transaction handle. Intra-transaction SQL reuses the same underlying transaction in sequence, which is suitable for multiple SQLs that require atomic commit or rollback.

## Syntax

```bt
tx = mysql(dsn).begin()
tx.query(sql).bind(value).exec()
tx.commit()
```

## Method

| Method | Parameters | Return value | Description |
| ------ | ------ | ------ | ------ |
| query(sql) | sql:String | MysqlTransaction | Set intra-transaction SQL. |
| bind(value1, value2, ...) | Any value | MysqlTransaction | Append binding parameters. |
| all() | None | Array | Execute a query and return multiple rows. |
| one() | None | Object/empty | Execute the query and return a single row; returns empty if there are no results. |
| exec() | None | Object | Execute writing SQL and return SQL execution statistics object; fields are shown below. |
| commit() | None | Bool | Commit the transaction and return true on success. |
| rollback() | None | Bool | Rollback the transaction, return true on success. |
| close() | None | Bool | The active transaction will be rolled back and closed; returns false if it has ended. |
| status() | None | String | Returns active, committed, rolled_back, closed, or failed. |
| sql(render_binds) | Bool, optional | String | Returns the SQL preview text; default render bind parameters. |

## Return value

| Type | Description |
| ------ | ------ |
| MysqlTransaction | query/bind Returns a new lightweight builder, but sharing the same transaction state. |
| Array | all Returns an array of row objects. For row object fields, see "Query row object fields" below. |
| Object/Empty | one returns a single row object when there is a result; returns `empty` when there is no result. For row object fields, see "Query row object fields" below. |
| Object | exec Returns the SQL execution statistics object. See "exec returns object fields" below for fields. |
| Bool | Status results of commit, rollback, close. |
| String | The text result of status or sql. |

## Query row object fields

The row objects returned by `all()` and `one()` are dynamic objects with fields derived from SQL result set column names.

| Field | Type | Must exist | Description |
| ------ | ------ | ------ | ------ |
| Any column name | Any | No | The column value returned by the database. The field name is the result set column name; when the field does not exist, the read result is `empty`. Database NULL returns `null`. |

## exec returns object fields

| Field | Type | Must exist | Description |
| ------ | ------ | ------ | ------ |
| total | Int | Yes | The number of bound rows processed by `exec()` within this transaction. `binds()` batch execution is not currently supported within a transaction, so it is currently fixed to `1`. |
| rows_affected | Int | Yes | The number of affected rows reported by the database. |
| last_insert_id | Int | Yes | The last insert ID reported by the database. |
| batch_count | Int | Yes | The actual number of batches split executed. Batch execution is not currently supported within a transaction, so it is currently fixed to `1`. |
| batch_size | Int | Yes | The batch size for the current execution. `batch()` is not currently supported within transactions, so it is currently `0`. |
| workers | Int | Yes | The number of concurrent workers used by the current execution. The same underlying transaction is reused sequentially within a transaction, so currently `1`. |

## Code Example

```bt
db = mysql('mysql://user:pass@127.0.0.1/test')
tx = db.begin()
tx.query('update user set name=? where id=?').bind('BT', 1).exec()
status = tx.status()
tx.rollback()

// Output: active
echo(status)
```

## Notes

- Do not reuse the same transaction object concurrently; an error will be reported if used again during transaction execution.
- Batch execution configuration of binds, batch, and workers is not currently supported within the transaction.
- Uncommitted transactions should call rollback or close; SQLx will attempt to rollback when the object is discarded, but the script should explicitly close the resource.
- `BT.stats().mysql.transactions_active` can be used to observe the current number of active transactions.