noSQL feature – ACID

See post on “variability”, the 4th V of big data.

A noSQL software could support transactions as RDBMS does, but the feature support is minimal in noSQL, according to the Oracle noSQL book.

Transactions slow down throughput, esp. write-throughput.

In a big data site, not all data items are high value, so ACID properties may not be worthwhile.

request/reply in one MOM transaction

If in one transaction you send a request then read reply off the queue/topic, i think you will get stuck. With the commit pending, the send won’t reach the broker, so you the requester will deadlock with yourself forever.

An unrelated design of transactional request/reply is “receive then send 2nd request” within a transaction. This is obviously for a different requirement, but known to be popular. See the O’Relly book [[JMS]]

2 phase commit (2pc) — homemade "implementation" idea

Phase 1 — reversible, preliminary, try-commit.
Phase 2 — irreversible final commit.

During phase 1, the XA manager issues (over the network) the try-commit command to all resource (nodes).

* If one of them didn’t come back (perhaps after a timeout), manager issues rollback command to all.
* if all successful, it issues the final-commit command.

Phase 2 should be a very simple, no-fail operation. If a resource (node) can’t support/provide such a never-fail operation, then it can’t qualify for XA transaction at all.

Another disqualified resource — If a node crashes badly during Phase 1, then rollback may be impossible and data loss might occur — this resource doesn’t qualify for XA.

Q: is the XA mgr a separate process or just an “object” in the same VM as a resource?
A: a process on the network

Q: is the XA mgr a network daemon that can receive incoming messages? Is it a multi-threaded network server?

tibrv supports no rollback – non-transactional transport

Non-transactional “transports” such as TIBCO Rendezvous Certified and socket do not allow for message rollback so delivery is not guaranteed.  Non-transactional transports can be problematic because the operation is committed to the transport immediately after a get or put occurs, rather than after you finish further processing and issue a Commit command.

JMS does support transactional “transport”, so you can “peek” at a message before issuing a Commit command to physically remove it from the queue.

Oracle article on exclusive/shared locks, row/table level locks, non-repeatable-read

Here are a small sample of the knowledge pearls —

– If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations (like alter table).

– A detailed comparison of read-committed vs serializable isolation levels in Oracle

To my surprise,

* “Readers of data do not wait for writers of the same data rows”. Maybe Reader thread simply goes ahead and read-committed. Reads either the BEFORE or the AFTER image of the row. Now, If isolation is at Serializable, then still no wait, but this thread will throw exception if it re-reads the row and detects update. This is a classic non-repeatable-read error.

read/write lock concept simplified, using countingSemaphore

Invariant to be enforced (Ignore the how-to-enforce please) — if a (exclusive) writer is in the critical section, then no other (reader/writer) thread should be seen in the same critical section.

Now a possible enforcement policy — using semaphore with a large number of permits. Any reader must get a permit before entering critical section; a writer must get all permits before entering.

We can learn a lot from DB transaction isolation levels.

transaction isolation levels, again has more details

This article shows simple but clear examples — One of the best articles on isolation levels.

Before seeing this article, I studied isolation levels 3 times until I got them right.

If you are CEO (or ticket master) of Northwest Airlines, a repeatable-read isolation level will assure a traveler “what you see on screen is available for the next 10 seconds”.
However, most Sybase systems default to one level lower – Level 2 i.e. read-committed.

nested transactions in sybase

If proc1 does “begin tran; exec proc2”, and proc2 does a “begin tran…”, how many transactions are active at that point? The way to test it in sybase is simple, open a session in sqsh, isql or aqua studio and

insert t … — a table
begin tran tr1
insert t…
commit tr1

— open another session and revoke insert permission

insert t… — causing an error and a rollback

— now see what’s rolled back.

y JMS session is Single-Threaded

1 session – 1 transaction.

How about database connection. 1 connection – 1 private memory space in the server to hold transaction data, so no multiple concurrent transaction. Even a select is part of a transaction, if you recall the isolation levels (repeatable read…) Usually, the same connection is not used in multiple concurrent threads, but no system enforcement.

How does any JMS system enforce the single-threaded rule? I doubt there’s any. If you break the rule, then result is perhaps similar to the c++ “undefined behavior”.

Note a Connection can be shared by threads, but a Session can’t. The java tutorial says “You use a connection to create one or more sessions.” Additionally, in the flow diagram anything upstream is thread-safe; anything downstream is single-threaded.

connFactory -> Conn -> Session -> producer/consumers

transaction isolation level (%%language)

Basic context behind the jargon:
* shared data — in memory or disk
* at least 2 threads. Isolation between threads.

0) concurrent, interleaved writes — no transaction. no isolation. 2 threads can each update half of a row’s data, corrupting it. I don’t think any DB allows this.

1) dirty read or “read uncommitted”(RU) — Easy. when a reader thread/session gets to see another thread’s uncommitted data change, that’s one count of read-uncommitted offense. We say this system[4] “allows read-uncommitted“. This is the lowest isolation level, but higher than (0) above.

2) read committed (RC) [3] — If system doesn’t expose uncommitted data change, then read-uncommitted will never occur. If system is configured this way, then /each thread only reads committed changes/. However, each thread doesn’t get repeatable-read-by-id guarantee.

3) repeatable read (RR) by id — Your thread reads an item by id and writes something /based/ on the read. If you can reliably read, read, read for a long time … to get the *same* data before your write, then you are lucky [2] — no other session is allowed to touch that object. You get Repeatable Read on that object[1] guarantee by the system. This system is operating in the RR mode.

Basically, system locks all rows of your first read and reserves them for you — pessimistic locking. [5] Once you read a row, it’s reserved for you. You are an emperor — once you cast your eyes on a girl, you have the right on her.

4) phantom read — However, if you read with a range-select like “where price > 0”, then your first read can reserve 500 rows and a repeated read can turn up 501 rows. This is a count of Phantom Read. I feel this query is not repeatable. It’s not a read-by-id, so RR is not relevant.

Solution for phantom read is — a range-lock rather than a row or page lock. Some say “serialize all threads that access a given range”.

Say Transaction1 has just finished a read with “from table1 where age > 22”, uncommitted. Can system allow Transaction2 to start, one that doesn’t mention this table? If system lets Transaction2 start, Transaction2 may lock up another table. Since Transaction1 locks up table1, there’s risk of deadlock. It’s safest to serialize all transactions.

[1] but not necessarily on that query
[2] luckier than the threads in a RC system
[3] RC is the default in most databases.
[4] Now the “system” could be a table or a database or a multi-threading app. There’s absolutely(?) no control, coordination, discipline or “isolation” in this RU mode. Isolation is the I in ACID.
[5] Now we know every SELECT is implicitly in a new transaction. In RR mode, it locks up all the rows involved until commit. This is more strict than the default RC mode.

real world DB deadlock reduction — insert hotspot

One of my multi-threaded java apps (using ExecutorSerivce) started getting database deadlocks when I increased thread count from 1 to 5.

* In Dev/Test environment, i got up to 4 deadlocks out of 20,000-40,000 inserts.
* In Production, I got 3000+ deadlocks out of 20,000 inserts.

Solution: I changed the clustered index from the table’s identity col to an AccountNumber column.

Result? down to below 5 deadlocks.

Analysis: Probably the clustered index on identity col created a hotspot for inserts. Since new rows all get similar identity values, all 5 threads need to write to the same data pages (sybase defaults to page lock). Each thread (with its own db connection and spid) in the deadlock probably writes to 2 pages within on transaction. If one thread already wrote on page 1 and needs to write on page 2 before commit(), but the other thread already wrote to page 2 and needs page 1, then this scenario meets

* wait-for circle
* incremental lock acquisition
* mutex
* non-preemption

lock scope vs db transaction scope

In my systems, i often call beingTran() in one method and commit() or rollback() in other methods. Now, a db transaction often locks a page[1], which is a shared resource. My design can lead to blocking or even deadlock.

java 5 lock scope is similar in one sense. You can call lock() in one method and unlock() in another method, so the lock scope spans methods. However, beware java deadlock is harder to detect than database deadlocks, and impossible to recover from. I feel you should consider tryLock() and lockInterruptibly().

[1] or rows, but let’s keep the discussions simple

to see who holds a DB lock

An experienced friend suggested “You can find the DB-loginname and the client machine connected to the DB server.” I don't
completely agree with this solution.

Very often an application loginname like “KP2_reporting” is used by 200 (realistic) applications, all by the same IT team? Many of
these applications could connect from the same production machine. At any time, up to 20 applications could be running.

I think we can also try to get the process id on the client machine, or the TCP port.

start/end of a unit of work — DB2

J4: it’s important to understand this (simple) concept when reading about savepoints, ….

Practically, at any (except right after a commit/rollback) moment in your session, you are in an open[1] transaction.

The DB2 trainer said a tx would start when u are outside any tx and issue a select or another sql statement. I still don’t understand.

[1] or “active transaction”

joining an existing transaction

an EJB method has 6 transaction attributes (described in tref). A basic concept is “joining a transaction”. It means “joining an existing transaction”.

This joining concept is important not only in EJB.

According to the DB2 trainer, DB2 triggers and functions JOIN transactions, whereas sproc often CONTROL transactions. Let’s explore

* a function is used in a single select.
* a trigger is invoked as part of a CUD

java transaction — brief AR notes

a tx is often “managed” on the server. There’s one session per tx in server memory. But how is a XA tx managed??

a tx and a session always involve a private mem, often on server and client

a tx usually (always?) involves a SESSION. perhaps a session with the DB or MOM
a tx usually (always?) involves a java THREAD.
a tx always involves a private mem

“XA-enabled jdbc driver”?? I guess such a driver understands and responds to the commands of global tx mgr.

A Weblogic expert told me that the first phase can be lengthy as all parties fully prepare their commit and rollback infrastructure. It can fail. 2nd phase is extremely quick as the global tx mgr somehow triggers all parties (geographically distant) to perform the final step. Because this last step is extremely _simple_, it’s virtually impossible to fail.

Q: what if one of the parties fails during 2nd phase, while other parties commit.
A: looks like it is a technical possibility but known to be extremely unlikely.

hibernate transaction — a comprehensive intro

— from, which also covers ejb, cmt, jdbc, interceptor …

1-sentence intro: Usually you also flush a Session at the end of a
unit of work to execute the SQL DML operations (UPDATE, INSERT,
DELETE) that synchronize the in-memory Session state with the

Hibernate disables or expects the environment (in J2EE/JEE) to disable
auto-commit mode

— sess vs tx
A single Hibernate Session might have the same scope as a single database transaction. DB server probably maintains a session for each connection. I would speculate

1 hib-session – 1 server-session

— hibernate && jta
Hibernate works in any environment that uses JTA, in fact, we recommend to use JTA whenever possible as it is the standard Java transaction interface. You get JTA built-in with all J2EE/JEE application servers, and each Datasource you use in such a container is automatically handled by a JTA TransactionManager.

— no jta@@
If you don’t have JTA and don’t want to deploy it along with your application, you will usually have to fall back to JDBC transaction demarcation. Instead of calling the JDBC API you better use Hibernate’s Transaction interface

Fwd: commit/rollback ] jms pub-sub means …

when the jms tx mgr executes a commit
– each msg produced in the tx is commited to the queue@@
– consumed msg is removed from queue@@
And the consumer must send ack to broker

when a rollback takes place
– msg produced in the tx is destroyed@@
– msg consumed in the tx is put back into the queue@@
And broker must /redeliver/ it (push instead of the point-to-point pull)

spring/hibernate transaction AOP interceptor

q 3: which obj intercepts the sql and manages tx propagation, session and thread to enable tx?
A3: “they are intercepted and interpreted by a particular implementation of the org.springframework.transaction.PlatformTransactionManager interface”.

AOP usually requires an interface, as needed by JDK proxy

For example, a hibernate tx mgr is such an interceptor — org.springframework.orm.hibernate.HibernateTransactionManager

Q82: where are “interceptor” and “proxy” mentioned in src?